mysql replication (Error reading packet from server: Binary log is not open (read_errno 0,server_errno=2000)
021009 12:56:49 mysqld started /usr/sbin/mysqld: ready for connections 021009 12:56:49 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 021009 12:56:49 Error reading packet from server: Binary log is not open (read_errno 0,server_errno=2000) 021009 12:56:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 021009 12:56:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 021009 12:56:49 Error reading packet from server: Binary log is not open (read_errno 0,server_errno=2000) Im doing mysql replication, i cant figure this error Binary log is not open ? -- thanks, louie miranda chikka asia, inc. noc +63-2(7535000-511) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query help
Maybe I'm way out here, but this happens to me (or similar) quite often. My solution; SELECT DISTINCT And a single row will be returned for each. Niclas On Tuesday 08 October 2002 21:48, Michael Knauf/Niles wrote: Here's a query for ya: SELECT products.product_name, products.fg_number, products.product_description, specs.spec_name, specs.spec_value FROM products LEFT JOIN specs ON products.fg_number = specs.fg_number WHERE products.fg_number='fg00914'; and here's the result: +--+---++-- +--- + | product_name | fg_number | product_description| spec_name| spec_value +--+---++-- +--- + | | fg00914 || Woofer | 8 injection-molded TCC (talc) test | | fg00914 || Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | | fg00914 || Recommended amplifier power | 10 to 150 watts | | fg00914 || Frequency | 40Hz-21kHz +/- 3dB | | fg00914 || Sensitivity | 90dB For 2.83V pink noise | | fg00914 || Impedance | 4 or 8 ohm selectable | | fg00914 || Dimensions | 10-5/8 diameter | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Woofer | 8 injection-molded TCC (talc) test | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Recommended amplifier power | 10 to 150 watts | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Frequency | 40Hz-21kHz +/- 3dB | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Sensitivity | 90dB For 2.83V pink noise | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Impedance | 4 or 8 ohm selectable | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Dimensions | 10-5/8 diameter +--+---++-- +--- + 14 rows in set (0.00 sec) I get 7 rows of data I don't want, then the 7 rows of data I do want... and after scratching my head for awhile, I've come to the conclusion that I do not understand the query structure. (the query came from multiple suggestions in response to my last posting on this list, so clearly it makes sense to some of you...) Can somebody point me to a resource on complicated multi-table select statements? I have the Paul DuBois MySQL book, which got me from 0 to where I am now, but isn't getting me past this particular query. Or maybe just explain how to get the last 7 rows without the first? --it's duplicating the same data with and without the product_name and product_description fields. Here's the table structure: mysql describe products; +-+--+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra +-+--+--+-+-+-- --+ | id | int(6) | | PRI | NULL| auto_increment | | fg_number | varchar(9) | | MUL | 0 | | | product_name| varchar(64) | | MUL | | | | product_description | varchar(255) | | | | +-+--+--+-+-+-- --+ 4 rows in set (0.00 sec) mysql describe specs; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(12) | | PRI | NULL| auto_increment | |
eroor: when connecting remote system using userid and password on red hat linux
Dear Sir,
When Iam trying to connect remote mysql database Using
JSP on LINUX Os it is giving an error...
Remote system is Windows NT.
java.sql.SQLException: Communication link failure: Bad
handshake
I loaded
1).MySQL-3.23.40-1.i386.rpm
2).MySQL-client-3.23.40-1.i386.rpm
3).mm.mysql.jdbc-12b.tar driver
files in to my system. Is any other files required to
install?
How i will overcome this problem . My OS is LINUX Red
Hat.
My JSP source code is like this:
%@ page language=java import=java.sql.* %
%
Connection Conn = null;
ResultSet RS = null;
Statement Stmt = null;
String DBUrl = jdbc:mysql://IPaddress:3306/db
name?user=usernamepassword=pass;
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
Conn = DriverManager.getConnection(DBUrl);
Stmt = Conn.createStatement();
RS=Stmt.executeQuery(select * from HITS);
while(RS.next())
{
%
br
%
out.println(RS.getString(1));
}
%
__
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos More
http://faith.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
drop constraint in innodb
sql, query Hi Folks ALTER TABLE main_db ADD CONSTRAINT FOREIGN KEY( Code, Kana) REFERENCES Syoyu_db( Code, Kana) ON DELETE CASCADE ; works fine. But how to drop the constraint or modify the constraint ? Simply changing ADD to DROP or MODIFY throws errors regards - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld Threads with Innobase
Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mytop - DBD::mysql failed to load
Hi, I try to install mytop in order to get some information about my MySQL database, after I installed : - DBI-1.18.tar.gz - Msql-Mysql-modules-1.2216.tar.gz - Data-Dumper-2.101.tar.gz - Data-ShowTable-3.3.tar.gz ...and 'perl Makefile.PL' I get this warning: Warning: prerequisite DBD::mysql failed to load: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.6.1/i586-linux /usr/lib/perl5/5.6.1 /usr/lib/perl5/site_perl/5.6.1/i586-linux /usr/lib/perl5/site_perl/5.6.1 /usr/lib/perl5/site_perl .) at (eval 4) line 3. Writing Makefile for mytop I don't know what to do? Somebody has an answer to this problem? Thanks a lot, Sebastien www.esnig.ch E-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT max value...
Bill Hess wrote: Hello all... What is the maximum value of an AUTO_INCREMENT field? I am currently running into a maximum value of 296,995,724 on Solaris 8 using MySQL 3.23.42 - the field has been defined as an INT(11), which should be able to handle a value bigger than what I am seeing - The DuBois MySQL book states it should handle 2^31 which turns out to be 2,147,483,648 Is there another setting specifc for AUTO_INCREMENT? If I changed the field's datatype to BIGINT would I be able to achieve higher values? Does anyone out there have other techniques they use in place of AUTO_INCREMENT? Please include my direct email when repsonding - Thanks... Hello, The maximum value depends on the type of the field. It is 2^31-1 for INT, 2^63-1 for BIGINT. Check the manual for more details: http://www.mysql.com/doc/en/Numeric_types.html Regards, -- Joseph Bueno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Demande d'inscription à mysql-france
Bonjour, Vous avez récemment demandé à vous inscrire au groupe mysql-france. Comme vous êtes déjà membre de ce groupe, votre demande ne sera pas prise en compte. Pour toute question sur l'envoi de messages à ce groupe, veuillez envoyer un e-mail à [EMAIL PROTECTED] Cordialement, L'équipe support Yahoo! Groupes L'utilisation du service Yahoo! Groupes est soumise à l'acceptation des Conditions d'utilisation et de la Charte sur la vie privée, disponibles respectivement sur http://fr.docs.yahoo.com/info/utos.html et http://fr.docs.yahoo.com/info/privacy.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to get the lists of DB from MySQL system tables?
Hi Guys, I'm using MySQL for Windoze (latest version of Server ODBC). Is it possible to get the lists of DB in the Windows using the system tables of MySQL? or any workaround you can suggest. Many thanks in advance. Regards, Jojo __ Do You Yahoo!? Great flight deals, travel info and prizes! http://sg.travel.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
connect failed from external
Hi, I failed to connect mysql from external network (it works by connected . In the error log, I found the following message. Number of processes running now: 1 mysqld process hanging, pid 13523 - killed 021009 12:35:28 mysqld restarted /usr/libexec/mysqld: ready for connections As you can see, mysql restart itself when I try to connect from external network. I found the it happened after I upgrade glibc to 2.2.5-40 from redhat network. I wonder anyone get the same experience. Instead of downgrade to 2.2.5-39, anyone can tell me how to solve it? unplug -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to use comma instead dot in numeric values?
Is there a simple solution to define MySQL to use comma instead of dot in numeric fields? Sincerely, Putte koivisto - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CPU Load
sql spam filter Hi I'm running MySQL-3.23.37 on a cobalt raq. I have a table with approx 40,000 records and a dozen users running simple queries and updates - names, addresses that kinda thing, but every time a query is run mysql takes everything it can get and the box freezes: 10:33am up 12:18, 1 user, load average: 0.37, 0.55, 1.74 66 processes: 64 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 71.5% user, 20.5% system, 0.0% nice, 7.9% idle Mem: 517188K av, 207384K used, 309804K free, 50216K shrd, 13508K buff Swap: 131532K av, 40240K used, 91292K free 122896K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 28351 mysql 10 0 7064 5924 856 S 0 77.3 1.1 1:41 mysqld Is there somethig I need to optimise?? I'm using the as supplied my_large.cnf. Any help, suggestions gratefully rec'd Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb
Description: Hello, We reported a problem about selecting fields that some of them didn´t belong to the used index on September. We have already installed MySql 4.0.4 and we have a similar problem (and also we are still having the original problem) when we select fields which some of them don´t belong to the primary index that MySql should use in the search. In this query, we haven´t any OR condition in the WHERE clause as we had in the query that we made on September. Moreover, in this case MySQL doesn´t use any index when runs the query. So, we think this problem is bigger than the previous one. We have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (albaranes.txt) in a compressed file named Albaranes.zip so you can reproduce the bug. We hope the test we are going to explain can help you to fix the bug. Now, the table definition is (we have got 45055 records) CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', `Fac` varchar(8) NOT NULL default '', `Ped` varchar(8) NOT NULL default '', `Pis` char(2) NOT NULL default '', `Fec` date default NULL, `Dom` char(2) NOT NULL default '', `FoP` char(2) NOT NULL default '', `Ven` char(3) NOT NULL default '', `Rep` char(3) NOT NULL default '', `Ba1` decimal(20,4) NOT NULL default '0.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `Iv1` float NOT NULL default '0', `Iv2` float NOT NULL default '0', `Iv3` float NOT NULL default '0', `Iv4` float NOT NULL default '0', `Re1` float NOT NULL default '0', `Re2` float NOT NULL default '0', `Re3` float NOT NULL default '0', `Re4` float NOT NULL default '0', `Dps` decimal(20,4) NOT NULL default '0.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.', `TCR` decimal(20,4) NOT NULL default '0.', `TBe` decimal(20,4) NOT NULL default '0.', `Nts` text, `GAS` decimal(20,4) NOT NULL default '0.', `ENV` varchar(40) NOT NULL default '', `NFV` tinyint(4) unsigned NOT NULL default '0', `NFR` tinyint(4) unsigned NOT NULL default '0', `Usuario` char(2) NOT NULL default '', `Tar` varchar(6) NOT NULL default '', `Umv` date default NULL, `Retenido` tinyint(4) unsigned NOT NULL default '0', `Total` tinyint(4) unsigned NOT NULL default '0', `Tipo_portes` tinyint(4) unsigned NOT NULL default '0', `Agencia` char(2) NOT NULL default '', `Nom_agencia` varchar(255) NOT NULL default '', `Peso` float NOT NULL default '0', `Volumen` float NOT NULL default '0', `Num_expedicion` varchar(20) NOT NULL default '', `Instalacion` varchar(20) NOT NULL default '', `Bultos` smallint(6) NOT NULL default '0', `Doc_manual` varchar(8) NOT NULL default '', `Integrado` tinyint(4) unsigned NOT NULL default '0', `Su_Pedido` varchar(40) NOT NULL default '', `RecalculaPreciosCompra` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), UNIQUE KEY `FecCliAlb` (`Cli`,`Fec`,`Alb`), UNIQUE KEY `PorInstalacion` (`Cli`,`Instalacion`,`Fec`,`Alb`), UNIQUE KEY `PorSuPedido` (`Cli`,`Su_Pedido`,`Fec`,`Alb`), KEY `Facturas` (`Fac`) ) TYPE=InnoDB; And now the tests: INNODB == 1) explain select * from albaranes where alb 'SA' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=45240,Extra=NULL. Time: 0.85 secs. MySql must use the PRIMARY index !!! 2) explain select alb from albaranes where alb 'SA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=NULL,rows=58698,Extra=where used; Using index. Time:0.05 secs. If we only select the ALB field which belongs to the PRIMARY INDEX, the query runs okey. 3) explain select * from albaranes where alb 'BA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=null,rows=41440,Extra=where used. Time:0.10 secs. The records with alb 'BA' are at the beginning of the table. This is the difference between explain 1 and explain 3. 4) explain select * from albaranes where alb 'MA' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=45240,Extra=where used. Time: 0.80 secs. MySql must use the PRIMARY index !!! 5) explain select * from albaranes where alb 'SB' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=null,rows=16224,Extra=where used. Time:0.09 secs. The records with alb'SB' are at the end of the table. 6) explain select * from albaranes where alb 'SB' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=44499,Extra=where
mysql.server Mods for Multiple Instances/Users
What are the issues if I were to remove the following snippets from mysql.server on a RH 7.2 system running multiple instances of MySQL under multiple users?: # Make lock for RedHat / SuSE if test -w /var/lock/subsys then touch /var/lock/subsys/mysql fi # delete lock for RedHat / SuSE if test -f /var/lock/subsys/mysql then rm /var/lock/subsys/mysql fi Thank You, Anthony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to rebuild indexes..
It says in the manual .. recompile MySQL, and rebuild your FULLTEXT indexes First time I downloaded the source code and compiled it, I just deleted the old indexes and created new ones. But now I have a lot of tables with fulltext indexes. So I'm looking for an easy way to do it. Searched through the documentation for something like 'rebuild indexes'/'restore indexes' but haven't found anything yet. Is there an easy way to rebuild all the indexes? I'm going to write a small file with sql statements to do it if there ain't any other way. Erlend Stromsvik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT max value...
But did you check if the file size is at 4Gig? This will cause strange things to happen, mysql does not gice a nice error for this condition. If the table was not created with MAX_ROWS option = to some real large number like 5000, then you could be running into a problem with the size of the MYD or MYI file. You can use the ALTER statement to add this option to the table. Hope this helps, Ken - Original Message - From: Bill Hess [EMAIL PROTECTED] To: Keith C. Ivey [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 08, 2002 4:52 PM Subject: Re: AUTO_INCREMENT max value... Not really getting errors but getting warnings when inserting records and having duplicate values since the primary key is the one that is auto incremented... Keith C. Ivey wrote: On 8 Oct 2002, at 12:23, Bill Hess wrote: What is the maximum value of an AUTO_INCREMENT field? I am currently running into a maximum value of 296,995,724 on Solaris 8 using MySQL 3.23.42 - the field has been defined as an INT(11), which should be able to handle a value bigger than what I am seeing - The DuBois MySQL book states it should handle 2^31 which turns out to be 2,147,483,648 What is the error message you're getting? Could it be that the problem is the actually size of your data file (over 4 GB?) rather than the value of the AUTO_INCREMENT? [Filter fodder: SQL] -- Bill Hess Technology Resource Group - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: drop constraint in innodb
Last week, I posted a message related to this. Heikki (InnoDB Developer) said sorry, DROP CONSTRAINT is not implemented yet. - Original Message - From: kayamboo [EMAIL PROTECTED] To: list mysql [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 2:45 PM Subject: drop constraint in innodb sql, query Hi Folks ALTER TABLE main_db ADD CONSTRAINT FOREIGN KEY( Code, Kana) REFERENCES Syoyu_db( Code, Kana) ON DELETE CASCADE ; works fine. But how to drop the constraint or modify the constraint ? Simply changing ADD to DROP or MODIFY throws errors regards - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: storing PDF files in mysql ?????
Hi, The thing I was wondering is whether there is any advantage in saving IMAGES in a database? I have had a little play around with a 50k jpeg file and it appears that if I save it in Blob format, then the filesize of the database increases by 50K as well, and surely converting it from/to a database just add time to the loading of a page, albeit it very minimal, but as I am designing a site which will host huge numbers of photos, this will make a big difference. Any thoughts or advice on this topic will be most appreciated. Regards Alexis -Original Message- From: kayamboo [mailto:[EMAIL PROTECTED]] Sent: 09 October 2002 01:20 To: toby z Cc: list mysql Subject: Re: storing PDF files in mysql ? I think if you decide to store in the blob, whatever it is, a pdf or jpeg or bmp, it is going to be in binary format. While you need to display it back, just convert them back to whatever format you like. Else there is always the choice of just storing the path in your database. By the way, which language you are using to write your applications. If it is delphi, I can send you some sample code regards - Original Message - From: "toby z" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 1:14 AM Subject: Re: storing PDF files in mysql ? thnx rich but u see the problem iz my clients want their text IN pdf format to be displayed with all these colors on the page in other words they want their sites layout to be maintained on the pages showing this pdf text as they are pretty concerned bout security so what do i do now thnx a mill toby From: "R.Dobson" [EMAIL PROTECTED] To: toby gibbson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: storing PDF files in mysql ? Date: Tue, 08 Oct 2002 16:35:56 +0100 hi, 2 ways to do this: a) don't store the file actually in the db but store the path to it. b)use the function LOAD_FILE(filename) to get the contents of a file as a string value and store as a blob. decide which to use depending on how many pics you have cheers Rich toby gibbson wrote: ok guyz more trouble now im supposed to convert all the text (from ms word) to PDF and put the pdf files in mysql db now i ve really got myslf stuck havent i can anyone plz hlp thnx a million stuck az usual toby . _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: drop constraint in innodb
kayamboo, Wednesday, October 09, 2002, 8:45:39 AM, you wrote: k ALTER TABLE main_db ADD CONSTRAINT FOREIGN KEY( Code, Kana) REFERENCES k Syoyu_db( Code, Kana) ON DELETE CASCADE ; k works fine. k But how to drop the constraint or modify the constraint ? k Simply changing ADD to DROP or MODIFY throws errors Curretly you can't drop only foreign key constarints. You should re-create a table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: RESET MASTER during daily backups
Russell, Wednesday, October 09, 2002, 1:44:14 AM, you wrote: RU we use the binary log as a crash recovery tool. RU therefore, once we have backed up the db (we use the excellent mysql_backup for this) we can happily discard yesterday's binlog. RU the only correct way i've found to get rid of old binlogs in to issue RESET MASTER, and i figure i should do this at the same time RU that the logs get flushed. RU however, neither mysqldump nor mysqladmin have a RESET MASTER option. RU has anyone found a good way to do this? It's not an option, it's a command that you execute from client, for example: mysql RESET MASTER; Query OK, 0 rows affected (0.01 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQL install Problem?
Daniel, Tuesday, October 08, 2002, 11:14:06 PM, you wrote: DM I have am having problems getting the MySQL server to start running, please help me, here are my problems, I have also displayed my error logs below, at least I hope they are the error logs; DM I have been trying to install the DM mysql-3.23.52-pc-linux-gnu-i686.tar.gz DM Everything seems to be going smoothly until I entered the command DM ./scripts/mysql_install_db DM I got this output which i believe is ok; [skip] DM 021007 18:05:01 mysqld started DM 021007 18:05:01 /usr/local/mysql/mysql/bin/mysqld: Table 'mysql.host' DM doesn't exist DM 021007 18:05:02 mysqld ended DM 021007 19:01:35 mysqld started DM 021007 19:01:35 /usr/local/mysql/mysql/bin/mysqld: Can't find file: DM './mysql/host.frm' (errno: 13) DM 021007 19:01:35 mysqld ended [skip] DM I believe I have viewed my err logs but I still don't know what I have to do to get MySQL server to start working? What must I do to get the MySQL server to start working? Error 13 means permission denied Check permissions on the MySQL data dir. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: How to use comma instead dot in numeric values?
Putte, Wednesday, October 09, 2002, 12:04:25 PM, you wrote: PK Is there a simple solution to define MySQL to use comma instead of dot in PK numeric fields? You can't use ',' in numeric values. If you want to use ',', you can store as a string. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Query content question
ir000387, Tuesday, October 08, 2002, 11:22:51 PM, you wrote: iamdc I am trying to find a way to store the body of a binary NNTP message into iamdc MySQL. However, due to the way lines are encoded, MySQL will not accept the iamdc SQL statement. Does anyone have any ideas on how I should approch this? NNTP message is a simple text message. You can store it in the TEXT fieds, or BLOB: http://www.mysql.com/doc/en/BLOB.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: table damaged?
speters, Wednesday, October 09, 2002, 2:09:30 AM, you wrote: samdc I think i have a damaged table in one of my databases. samdc I can do a SELECT count(*) from table samdc on every table in the db with no problem, except for 1 samdc of them, the query just sits for a while. samdc So i assume this table is damaged. does anyone have any suggestions as to samdc what i can do about this? Use CHECK TABLE to check if your table is corrupted: http://www.mysql.com/doc/en/CHECK_TABLE.html What is the type of this table? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: How to get the lists of DB from MySQL system tables?
Jojo, Wednesday, October 09, 2002, 12:00:07 PM, you wrote: JM I'm using MySQL for Windoze (latest version of Server ODBC). JM Is it possible to get the lists of DB in the Windows using the system JM tables of MySQL? If you what to get a list of databases just use SHOW DATABASES command: http://www.mysql.com/doc/en/SHOW.html But how MySQL system tables is related to list of databases? ;) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQL
Jeremy, Tuesday, October 08, 2002, 8:55:35 PM, you wrote: JOn I am currently try to set up mysql on my computer and I'm running into a JOn few bugs. I'm using the book PHP fast easy web development. I've JOn run into a problem when I type the command mysqld --standalone into the JOn prompt. I get the following error: JOn Can't initialize InnoDB as 'innodb_data_file_path' is no set You need to specify innodb_data_file_path in my.cnf/my.ini: http://www.mysql.com/doc/en/InnoDB_start.html JOn I'm following the directions step by step, yet it's not working. I was JOn hoping you could possibly help me. Thank you. What exactly did you do? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQLGUI 1.7.5-1 problem
Martín, Tuesday, October 08, 2002, 8:29:21 PM, you wrote: MF I have downloades the last version of your software, but when I try to MF connecto to the mysql server remotly, the server crash, and safe_mysqld MF restar it. Your software reports lost connection to MySQL dureing query MF And the safe_mysqld reports dureing the crash: MF bash-2.05# /usr/local/mysql/bin/safe_mysqld: line 273: 3021 Violación de MF segmento $NOHUP_NICENESS $ledir/$MYSQLD MF $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR MF $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 MF Any idea what is happening? What OS do you use? What version of MySQL do you use? How did you installed MySQL: from binary or from source? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: PHP/mySQL - displaying length of time used by query
Shaun Bramley [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Hello all, I am using mySQL and PHP, how can I use PHP to display the length of time used by a query? Shaun, ? // grab the start time $start = time(); // db query goes here $res = mysql_query(...); // grab the end time $end = time(); // work out the time taken in ms from the two timestamps $timetaken = $start - $end; ? HTH. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: storing PDF files in mysql ?????
thnx guyz 1) i m tryin to put the pdf file into a blob field but i cant insert it like normal text how shall i go about inserting it 2) i m in the process of converting the word doccuments to pdf got this acrobat disteller n m havin a million probz with that alone like the security issue I NO THIS IS THE WRONG LIST SORRY FOR THIS Q BUT .. has anyone worked with this thing n wouldnt mind me mailin him/her plz do lemme no guyz plz hlp . thnx a billion toby From: Sqlcoders.com Programming Dept [EMAIL PROTECTED] To: toby z [EMAIL PROTECTED],list mysql [EMAIL PROTECTED] Subject: RE: storing PDF files in mysql ? Date: Tue, 8 Oct 2002 21:47:23 -0600 Toby, If I understand correctly you want to perform two distinct actions: 1. Convert Microsoft Office Word .doc files into .pdf 2. Store those pdf's in MySQL. You should look into (via google.com) some doc to pdf converters. An example is this com object: http://www.convertzone.com/doc2pdfcom/help.htm#HowtoBuy, it costs $219 and converts .doc to .pdf. If you can convert the .doc's into .html files, you might be able to get away with using Adobe's free email converter service. Email the HTML document to [EMAIL PROTECTED], wait an hour or so, and you'll find a pdf version in your inbox. (There's also a [EMAIL PROTECTED] address if you dont want to use html, but I think you wanted to keep formatting). Once you have the document in PDF, it's a relatively trivial action to store the documents in your MySQL database. HTH, William. -Original Message- From: kayamboo [mailto:[EMAIL PROTECTED]] Sent: 08 October 2002 18:20 To: toby z Cc: list mysql Subject: Re: storing PDF files in mysql ? I think if you decide to store in the blob, whatever it is, a pdf or jpeg or bmp, it is going to be in binary format. While you need to display it back, just convert them back to whatever format you like. Else there is always the choice of just storing the path in your database. By the way, which language you are using to write your applications. If it is delphi, I can send you some sample code regards - Original Message - From: toby z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 1:14 AM Subject: Re: storing PDF files in mysql ? thnx rich but u see the problem iz my clients want their text IN pdf format to be displayed with all these colors on the page in other words they want their sites layout to be maintained on the pages showing this pdf text as they are pretty concerned bout security so what do i do now thnx a mill toby From: R.Dobson [EMAIL PROTECTED] To: toby gibbson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: storing PDF files in mysql ? Date: Tue, 08 Oct 2002 16:35:56 +0100 hi, 2 ways to do this: a) don't store the file actually in the db but store the path to it. b)use the function LOAD_FILE(filename) to get the contents of a file as a string value and store as a blob. decide which to use depending on how many pics you have cheers Rich toby gibbson wrote: ok guyz more trouble now im supposed to convert all the text (from ms word) to PDF and put the pdf files in mysql db now i ve really got myslf stuck havent i can anyone plz hlp thnx a million stuck az usual toby . _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing PDF files in mysql ?????
Alexis Antonakis wrote: The thing I was wondering is whether there is any advantage in saving IMAGES in a database? None whatsoever unless you can't do remote file access any other way. # SQL: SELECT name FROM images LEFT JOIN table1 ON table1.imageid = images.id; # Program: open(name) off filesystem, or: # IMG SRC=./images/$name ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
crossover replication
If my mysql master is under Linux and my slave under Windows, if the master crashes, can I make the slave a master once I recover the Linux machine even if data is replicated 100%? Does replication only involves data? Would there be issues that would make my Windows slave incompatible as a master under Linux? thanks __ Post your free ad now! http://personals.yahoo.ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
information about the tables in the database
Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db thanks :) Inbal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Spans Multiple Pages
-Original Message- From: William Martell [mailto:[EMAIL PROTECTED]] I am trying to query MySQL using PHP and I would like the results to display on multiple pages. I would like to display only 20 results per page and allow the user access to the other pages by clicking next or an index of numbers 1 2 3 4 5 6 and so on dependent upon the number of results returned. I do not know how to do this?? Can anyone point me in the right direction??? First you would probably query for the number of records that would be returned. That way you can create your navigation bar with the correct amount of increments ( 1 2 3 4 5 6 ). Each increment = 20 records. Then you can perform queries using LIMIT ( LIMIT 0,19 ... LIMIT 20,39 ... LIMIT 40,59 etc ). Also, When a user does perform this query, will all of the pages be created at that time. Or will the page be created with another request and response from the server. One page at a time using LIMIT. You could create all the pages at once, but where are you going to put them, how are you going to serve them... I think you should just query each page of data when the user wants it. In other words. Does the result of the query get stored in a variable that is accessed on the client machine or does the php code return the limit per Hey look, you almost got a solution yourself... Luc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
indexing column type SET
Hi,
I have a question about indexing a column of type SET. It seems that
generally the index is not too useful, since it always requires some
sort of expression to get what you want out of the set, and therefore
the index won't be used.
Example:
create table FOO (BAR set('a','b','c','d'));
create index IDXBAR on FOO(BAR);
Let's say I want to select the rows where BAR contains the set value 'a'.
select count(*) from FOO where FIND_IN_SET('a',BAR) 0;
The above does not use the index since FIND_IN_SET() is an expression.
select count(*) from FOO where BAR 1;
Again, BAR 1 is an expression, no index used.
select count(*) from FOO where BAR like 'a%';
The above seems to work, although this approach won't work for many
cases. What if I want to find where the column contains 'b' and 'd' ?
select count(*) from FOO where BAR in (1,3,5,7,9,11,13,15);
This works but making a list is not practical, and the list size would
grow exponentially as you add set members.
So is there a way to do what I'm trying to do? If there is no way, how
difficult would it be to add this to MySQL?
I'm not sure of the best way to go about it, but one idea is internally
replace FIND_IN_SET('a',BAR) with BAR IN (1,3,5,7,...), ie. let
MySQL handle the calculation work so a SET can use an index in a
practical case.
Monte
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql replication (Error reading packet from server: Binary logis not open (read_errno 0,server_errno=2000)
Looks like you don't have binary logging turned on on the master. louie miranda wrote: 021009 12:56:49 mysqld started /usr/sbin/mysqld: ready for connections 021009 12:56:49 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 021009 12:56:49 Error reading packet from server: Binary log is not open (read_errno 0,server_errno=2000) 021009 12:56:49 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 021009 12:56:49 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 021009 12:56:49 Error reading packet from server: Binary log is not open (read_errno 0,server_errno=2000) Im doing mysql replication, i cant figure this error Binary log is not open ? -- thanks, louie miranda chikka asia, inc. noc +63-2(7535000-511) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: storing PDF files in mysql ?????
Hi, I do not recommend saving the image data in the DB: 1. The size of each DB Row increases dramatically. Depending on the structure of your tables, query speed drops as the rows are located further away on the disk (disk seek times) 2. For each image request, you have to involve some dynamic module (php, perl, java,...) that generates the image and takes up DB resources. On high frequented sites this can be a significant factor. Of course you could also write a custom cache for that. Regards, Kai query, sql -Ursprungliche Nachricht- Von: Alexis Antonakis [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 9. Oktober 2002 13:57 An: list mysql Betreff: RE: storing PDF files in mysql ? Hi, The thing I was wondering is whether there is any advantage in saving IMAGES in a database? I have had a little play around with a 50k jpeg file and it appears that if I save it in Blob format, then the filesize of the database increases by 50K as well, and surely converting it from/to a database just add time to the loading of a page, albeit it very minimal, but as I am designing a site which will host huge numbers of photos, this will make a big difference. Any thoughts or advice on this topic will be most appreciated. Regards Alexis -Original Message- From: kayamboo [mailto:[EMAIL PROTECTED]] Sent: 09 October 2002 01:20 To: toby z Cc: list mysql Subject: Re: storing PDF files in mysql ? I think if you decide to store in the blob, whatever it is, a pdf or jpeg or bmp, it is going to be in binary format. While you need to display it back, just convert them back to whatever format you like. Else there is always the choice of just storing the path in your database. By the way, which language you are using to write your applications. If it is delphi, I can send you some sample code regards - Original Message - From: "toby z" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 1:14 AM Subject: Re: storing PDF files in mysql ? thnx rich but u see the problem iz my clients want their text IN pdf format to be displayed with all these colors on the page in other words they want their sites layout to be maintained on the pages showing this pdf text as they are pretty concerned bout security so what do i do now thnx a mill toby From: "R.Dobson" [EMAIL PROTECTED] To: toby gibbson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: storing PDF files in mysql ? Date: Tue, 08 Oct 2002 16:35:56 +0100 hi, 2 ways to do this: a) don't store the file actually in the db but store the path to it. b)use the function LOAD_FILE(filename) to get the contents of a file as a string value and store as a blob. decide which to use depending on how many pics you have cheers Rich toby gibbson wrote: ok guyz more trouble now im supposed to convert all the text (from ms word) to PDF and put the pdf files in mysql db now i ve really got myslf stuck havent i can anyone plz hlp thnx a million stuck az usual toby . _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual)
Re: crossover replication
On Wed, Oct 09, 2002 at 09:29:34AM -0400, Kevan Louou wrote: If my mysql master is under Linux and my slave under Windows, if the master crashes, can I make the slave a master once I recover the Linux machine even if data is replicated 100%? Yes. Does replication only involves data? I'm not sure what you mean by that. Would there be issues that would make my Windows slave incompatible as a master under Linux? Shouldn't be, no. As long as the Windows box can handle the traffic, you should be fine. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,371,498,488 queries (246/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld Threads with Innobase
[ start stupid question ] Does setting innodb_thread_concurrency to 1 imply that only one innodb thread will be working at any given time? So using this on SMP servers that you would like to be answering simultaneous queries is probably not what you would really like to be doing, no? [ end stupid question ] Owen On Wed, 2002-10-09 at 02:55, Heikki Tuuri wrote: Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- USMail: InterGuide Communications, 1611 Dexter Avenue, Ann Arbor, MI 48103 Phone:+1 734 997-0922 FAX:+1 734 661-0324 mailto:[EMAIL PROTECTED] http://www.interguide.com/~osm/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
On Wed, Oct 09, 2002 at 03:43:15PM +0200, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db 'show tables' thanks :) Inbal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autocommit in MySql/Innodb
How can i take AUTOCOMMIT default in my MYSQL Configuration with Innodb tablespace? Every time i login at MySQL i have to type SET AUTOCOMMIT = 0. I want this parameter be always 0. I don´t use AUTOCOMMIT. Thanks Jacob --- UOL Eleições 2002 - Todos os lances da disputa política http://eleicoes.uol.com.br/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
On Wed, 9 Oct 2002, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db Hi, I guess that's not possible. SHOW TABLES shows your tables in the database, but unfortunately you can't do something like SELECT * FROM (SHOW TABLES) WHERE Tables_in_xx = 'blahblah' Of course you can create a table that contains metadata for your tables, but you would have to update it manually. Or you could have an external program scanning your data directories for added/removed table files. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
show tables; On Wednesday, Oct 9, 2002, at 05:43 America/Anchorage, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: eroor: when connecting remote system using userid and passwordon red hat linux
kusuma guntupalli wrote:
Dear Sir,
When Iam trying to connect remote mysql database Using
JSP on LINUX Os it is giving an error...
Remote system is Windows NT.
java.sql.SQLException: Communication link failure: Bad
handshake
I loaded
1).MySQL-3.23.40-1.i386.rpm
2).MySQL-client-3.23.40-1.i386.rpm
3).mm.mysql.jdbc-12b.tar driver
files in to my system. Is any other files required to
install?
How i will overcome this problem . My OS is LINUX Red
Hat.
My JSP source code is like this:
%@ page language=java import=java.sql.* %
%
Connection Conn = null;
ResultSet RS = null;
Statement Stmt = null;
String DBUrl = jdbc:mysql://IPaddress:3306/db
name?user=usernamepassword=pass;
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
Conn = DriverManager.getConnection(DBUrl);
Stmt = Conn.createStatement();
RS=Stmt.executeQuery(select * from HITS);
while(RS.next())
{
%
br
%
out.println(RS.getString(1));
}
%
__
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos More
http://faith.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
This has been answered many times in the past few days on this list. Is
there a programming class assignment out there somewhere that people are
using? The question is always the same, from different people.
You are using a _very_ old version of MM.MySQL to connect to a newer
version of MySQL. They don't work together, because the network protocol
changed slightly.
What you need to do is download a newer version of MM.MySQL (which is
now MySQL Connector/J) from http://www.mysql.com/products/connector-j/
-Mark
--
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question about solving a query !
Hello, I have a problem to treat a query with MySQL because MySQL does not permit nested SELECT clauses. The schema of the table is : Hospital num | name | city| numbeds 10 | Clairval| Marseille | 70 20 | Notre-Dame | Marseille | 150 30 | Tonkin | Lyon | 90 40 | Charpennes | Lyon | 300 80 | AAA| Paris |1500 90 | BBB | Paris |1400 100| CCC| Paris | 300 The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . In fact, I can write (if I do not use MySQL): Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); But it does not work with MySQL. I'm looking for a MySQL version of this query, I will be very grateful if you help me !!! Thank you !! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT max value...
Nope - nothing close to 4GB or even 1GB... My greatest data file size is 80MB. The largest table is 1.7 million rows. I have databases with more and bigger files. I think my problem is that the table having the problem with auto increment is refreshed everyday (or even multiple times a day) by different automated programs running on various other machines. These updates run at different times and remove all of the records from the last run before repopulating the records. In most cases the largest value is pretty big so when a new recordis added the auto increment field just gets bigger and bigger since there is probably never a time where there are 0 records in the table. One would think that the value would not top out until 2^31 since I have it defined as a normal INT. I guess I can make it a BIGINT, but I think I will run into a problem eventually regardless how big I make the field... Ken Menzel wrote: But did you check if the file size is at 4Gig? This will cause strange things to happen, mysql does not gice a nice error for this condition. If the table was not created with MAX_ROWS option = to some real large number like 5000, then you could be running into a problem with the size of the MYD or MYI file. You can use the ALTER statement to add this option to the table. Hope this helps, Ken - Original Message - From: Bill Hess [EMAIL PROTECTED] To: Keith C. Ivey [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 08, 2002 4:52 PM Subject: Re: AUTO_INCREMENT max value... Not really getting errors but getting warnings when inserting records and having duplicate values since the primary key is the one that is auto incremented... Keith C. Ivey wrote: On 8 Oct 2002, at 12:23, Bill Hess wrote: What is the maximum value of an AUTO_INCREMENT field? I am currently running into a maximum value of 296,995,724 on Solaris 8 using MySQL 3.23.42 - the field has been defined as an INT(11), which should be able to handle a value bigger than what I am seeing - The DuBois MySQL book states it should handle 2^31 which turns out to be 2,147,483,648 What is the error message you're getting? Could it be that the problem is the actually size of your data file (over 4 GB?) rather than the value of the AUTO_INCREMENT? [Filter fodder: SQL] -- Bill Hess Technology Resource Group - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bill Hess Technology Resource Group - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
using php to connect to a 2nd mySQL server
Hello all, I am new to this list ... and hope to find an answer to a question which has come up. On the server for my websites at my ISP, a mySQL server is installed to handle different customers' databases (1 database setup for each customer) Now, as I have a need for a mySQL server where I can create/delete databases, my ISP agreed to install a 2nd mySQL server that can be dedicated to my sole use ... but I am told it would run on a different port number ... My question now is this: How can I tell a php script to not use the standard mySQL server that exists on this server computer, but to use the 2nd mySQL server that runs on the different port number ? Currently I use info set up in variables like this: # MySQL database config stuff $host=localhost; $username=abcdefg; $database=database; $password=123456789; Is it possible to add the port information somwhere? or does a php connection always use the standard mySQL port settings? Any help is greatly appreciated. Thanks! God bless you with His grace and peace Wolfgang Schneider -- BibelCenter: http://www.bibelcenter.de/ - Come and See! -- Bookstore: http://www.worthy.net/BibelCenter/ - Great offers! Check it out! -- email: mailto:[EMAIL PROTECTED] -- http://awesome.crossdaily.com/vote.php3?sid=952 -- Your Vote! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Calculating Recurring Dates
Hi all, Can anyone tell me how to do this? What I want to do is run a query that calculates the next [n]th [Sun - Sat] of [Jan - Dec] in MySQL? ie (Return the next occurance of the 2nd Tuesday in April). In my table I store the day of the week I want the event to occur on, which occurance in the month of that weekday, and what month I want it to recur for. Those values are all stored as numbers (DayOfWeek - 1 - 7, Which Occurance - 1-4, Month - 1 - 12). For these examples assume the current date is Wed, Oct 9, 2002. If I wanted to know the next occurance of the 3rd Thursday in December (ie repeattype = C, repeatinterval = 3, repeatday = 5, repeatmonth = 12), it would return a date of Dec 19, 2002. But if I wanted to know the next occurance of the 2nd Monday in April (ie repeattype = C, repeatinterval = 2, repeatday = 2, repeatmonth = 4), it would return a date of Apr 12, 2003. This is for an event tracking system which needs to allow for recurring events. The table format is like this: mysql describe events; ++-+--+-+-+- --- | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+- ---+ | eventnumber| int(11) | | PRI | NULL| auto_increment | | title | varchar(50) | YES | | NULL| | | eventdate | date| YES | | NULL| | | starttime | time| YES | | NULL| | | endtime| time| YES | | NULL| | | notes | longtext| YES | | NULL| | | location | longtext| YES | | NULL| | | repeatuntil| date| YES | | NULL| | | repeattype | char(1) | YES | | NULL|None, Daily, Weekly, Monthly, Yearly, Custom| | repeatinterval | int(11) | YES | | NULL|1 through 4 | | repeatday | int(11) | YES | | NULL|1 through 7 | | repeatmonth| int(11) | YES | | NULL|1 through 12 | ++-+--+-+-+- ---+ Any takers? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication issues
Hello List, I'm using MySQL 4.0.3-beta on 2 RedHat 7.3 Boxes belonging to a 2 node HA Cluster. To allow the takeover of the database node I formed a replication ring. So node1 replicates to node2 and the other way round. Read/Write operations are only done on Node 2. The nodes are connected with 1 Gbit Nic's The replication ring seems to work but I have another problem. With v 3.x the whole replication seemed much more stable. At least it run several days without an error. It was also possible to resume replication without any errors when a node was rebootet. Now with 4.0.3 the whole thing isn't that stable anymore. Mostly it is this error: When I shut down the master the slave complains that the query log is truncated. | 021007 22:54:31 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) | 021007 22:54:31 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary | log Another strange thing is that the slave doesn't seem to try to reconnect top the master after a server restart. I always have to restart the slave. LOAD DATA FROM MASTER doesn't work the way I expected as it constantly complains about the missing SUPER priviliege. But I did set it. Here the my.cnf of one of the nodes: mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_allowed_packet=16M [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqld] skip-innodb master-host=10.1.0.212 master-user=replicator master-password=secret server-id = 102 log-bin slave_net_timeout=120 I'm pretty sure that the problems are not directly associated with the replication itself but with my setup. Can anyone provide tips how to tune my setup? Please CC to me as I don't know if ezmlm allready added me to this list. Thanks in advance Daniel Khan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
On Wed, 9 Oct 2002, Brian Reichert wrote: On Wed, Oct 09, 2002 at 03:43:15PM +0200, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db 'show tables' But can you do a SELECT on the result set from that statement? Please let me know if it's possible. Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb
Rafa, I fixed the optimization of the ORDER BY ... LIMIT 50 query on the generated 'clientes' table you sent in September. The fix is in 3.23.53. It will favor index range scans even more over full index scans. But the underlying problem is in the MySQL optimizer. It does not realize that it can use the LIMIT to restrict the number of rows to be fetched. That may be fixed in 4.1 at the earliest. I will take a look at the optimizer problem below. Thank you, Heikki Innobase Oy - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 12:56 PM Subject: optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb Description: Hello, We reported a problem about selecting fields that some of them didn´t belong to the used index on September. We have already installed MySql 4.0.4 and we have a similar problem (and also we are still having the original problem) when we select fields which some of them don´t belong to the primary index that MySql should use in the search. In this query, we haven´t any OR condition in the WHERE clause as we had in the query that we made on September. Moreover, in this case MySQL doesn´t use any index when runs the query. So, we think this problem is bigger than the previous one. We have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (albaranes.txt) in a compressed file named Albaranes.zip so you can reproduce the bug. We hope the test we are going to explain can help you to fix the bug. Now, the table definition is (we have got 45055 records) CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', `Fac` varchar(8) NOT NULL default '', `Ped` varchar(8) NOT NULL default '', `Pis` char(2) NOT NULL default '', `Fec` date default NULL, `Dom` char(2) NOT NULL default '', `FoP` char(2) NOT NULL default '', `Ven` char(3) NOT NULL default '', `Rep` char(3) NOT NULL default '', `Ba1` decimal(20,4) NOT NULL default '0.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `Iv1` float NOT NULL default '0', `Iv2` float NOT NULL default '0', `Iv3` float NOT NULL default '0', `Iv4` float NOT NULL default '0', `Re1` float NOT NULL default '0', `Re2` float NOT NULL default '0', `Re3` float NOT NULL default '0', `Re4` float NOT NULL default '0', `Dps` decimal(20,4) NOT NULL default '0.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.', `TCR` decimal(20,4) NOT NULL default '0.', `TBe` decimal(20,4) NOT NULL default '0.', `Nts` text, `GAS` decimal(20,4) NOT NULL default '0.', `ENV` varchar(40) NOT NULL default '', `NFV` tinyint(4) unsigned NOT NULL default '0', `NFR` tinyint(4) unsigned NOT NULL default '0', `Usuario` char(2) NOT NULL default '', `Tar` varchar(6) NOT NULL default '', `Umv` date default NULL, `Retenido` tinyint(4) unsigned NOT NULL default '0', `Total` tinyint(4) unsigned NOT NULL default '0', `Tipo_portes` tinyint(4) unsigned NOT NULL default '0', `Agencia` char(2) NOT NULL default '', `Nom_agencia` varchar(255) NOT NULL default '', `Peso` float NOT NULL default '0', `Volumen` float NOT NULL default '0', `Num_expedicion` varchar(20) NOT NULL default '', `Instalacion` varchar(20) NOT NULL default '', `Bultos` smallint(6) NOT NULL default '0', `Doc_manual` varchar(8) NOT NULL default '', `Integrado` tinyint(4) unsigned NOT NULL default '0', `Su_Pedido` varchar(40) NOT NULL default '', `RecalculaPreciosCompra` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), UNIQUE KEY `FecCliAlb` (`Cli`,`Fec`,`Alb`), UNIQUE KEY `PorInstalacion` (`Cli`,`Instalacion`,`Fec`,`Alb`), UNIQUE KEY `PorSuPedido` (`Cli`,`Su_Pedido`,`Fec`,`Alb`), KEY `Facturas` (`Fac`) ) TYPE=InnoDB; And now the tests: INNODB == 1) explain select * from albaranes where alb 'SA' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=45240,Extra=NULL. Time: 0.85 secs. MySql must use the PRIMARY index !!! 2) explain select alb from albaranes where alb 'SA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=NULL,rows=58698,Extra=where used; Using index. Time:0.05 secs. If we only select the ALB field which belongs to the PRIMARY INDEX, the query runs okey. 3) explain select * from albaranes where alb 'BA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=null,rows=41440,Extra=where used. Time:0.10 secs. The records with alb 'BA' are at the beginning of the table. This is the difference between
Re: mysqld Threads with Innobase
Owen, - Original Message - From: Owen Scott Medd [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Nicholas Gaugler [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 5:44 PM Subject: Re: mysqld Threads with Innobase [ start stupid question ] Does setting innodb_thread_concurrency to 1 imply that only one innodb thread will be working at any given time? So using this on SMP servers that you would like to be answering simultaneous queries is probably not what you would really like to be doing, no? [ end stupid question ] only one thread is then allowed inside the InnoDB backend concurrently. But the threads also do work in the MySQL interpreter, and more importantly, in communication with the corresponding client application threads. Since 'thread thrashing' lowers performance 100-fold, also drastic measures like setting the InnoDB concurrency to 1 should be tried. Unfortunately, it does not usually help. The thrashing happens then outside the InnoDB backend. Owen Regards, Heikki On Wed, 2002-10-09 at 02:55, Heikki Tuuri wrote: Nicholas, - Original Message - From: Nicholas Gaugler [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 8:22 AM Subject: mysqld Threads with Innobase I know it's not very important in normal day to day operations, but does anyone know what each thread does within mysqld with Innobase installed? It'd be nice to know what Innobase is doing when a process is pinned at 100% of the cpu..if it's doing cleanup from a huge update, etc. /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my 1682 ttyp1S 0:00 /usr/local/sqldrive/mysql-4.0.4-beta/bin/mysqld --defaults-extra-file=/usr/l ocal/sqldrive/mysql-4.0.4-beta/data/my ... only one thread, the main thread, does background cleaning operations. You can see from SHOW INNODB STATUS what the main thread is currently doing. It is a good idea to tell the id of that thread in the printout of SHOW INNODB STATUS. I will add it to 4.0.5. In your case there are lots of threads eating up 100 % of CPU. If the performance is very slow, this looks like yet another case of Linux 'thread thrashing' which is plaguing both MyISAM and InnoDB tables under certain loads. It is a clear performace bug in Linux or glibc, and we are working to find a fix or a workaround to that problem. You can try setting set-variable = innodb_thread_concurrency = 1 in my.cnf to remove the thread thrashing. That helps in some cases Thanks! nickg Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: information about the tables in the database
show tables; show tables from catalogName; show tables from catalogName like 'order%'; -J -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Iikka Meriläinen Sent: Wednesday, October 09, 2002 10:09 AM To: Inbal Ovadia Cc: '[EMAIL PROTECTED]' Subject: Re: information about the tables in the database On Wed, 9 Oct 2002, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db Hi, I guess that's not possible. SHOW TABLES shows your tables in the database, but unfortunately you can't do something like SELECT * FROM (SHOW TABLES) WHERE Tables_in_xx = 'blahblah' Of course you can create a table that contains metadata for your tables, but you would have to update it manually. Or you could have an external program scanning your data directories for added/removed table files. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Kylux
Hi Silmara, You can use the dbExpress that comes with kylix. Silmara wrote: need use MySql to Linux ( Connectiva 7 )and Kylux. What version of the MySQL? Do I need MyODBC? -- -- \|/ __\|/ `@ / o . \ @' Microsoft? Por acaso é alguma nova /___| \/ |___\ marca de papel higiênico? \___U__/ .^. Luciano Barcaro - Linux User # 99517 /v\ Registre-se gratuitamente em // \\http://counter.li.org //(.)\\ ICQ # 17266954 ^`~'^ -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Displaying Default Values
I am useing mysql Ver 11.18 Distrib 3.23.52, for pc-linux-gnu (i686). I have set up a table in which one of the fields have a default value (i.e. New York). When I INSERT a record, that field is empty so that the default value is inserted into the field automatically (at least that's what I think). When I SELECT the all fields, the field with the default value is blank but when I SHOW or DESC the table, I can see the default value. Moreover, the field is set to NOT NULL. Has anybody seen this problem? Why I can't display the field's default value when execute the SELECT statement? -- Lawrence S. Stephens III [EMAIL PROTECTED] Paterson, New Jersey 07514-1711 USA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDb data file path
I am trying to set up two data files in case the first one fills up. I tried to use the following in my.cnf but it says there is an error. If I take out the reference to the second data file everything works AIX 4.3.3 MySQL 4.0.4 innodb_data_file_path=libdata1:100M:autoextend:max:2000M;libdata2:100M:autoextend:max:2000M Any help would be greatly appreciated. (query, Mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about solving a query !
Myoung-Ah KANG wrote: The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); select name, city, max(numbeds) from hospital group by city should work too, shouldn't it? -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
Sure you can. show tables like 's%' from mydatabase; Iikka Meriläinen wrote: On Wed, 9 Oct 2002, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db Hi, I guess that's not possible. SHOW TABLES shows your tables in the database, but unfortunately you can't do something like SELECT * FROM (SHOW TABLES) WHERE Tables_in_xx = 'blahblah' Of course you can create a table that contains metadata for your tables, but you would have to update it manually. Or you could have an external program scanning your data directories for added/removed table files. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT max value...
On 9 Oct 2002, at 11:55, Bill Hess wrote: One would think that the value would not top out until 2^31 since I have it defined as a normal INT. I guess I can make it a BIGINT, but I think I will run into a problem eventually regardless how big I make the field... Since the limit you're hitting has nothing to do with the maximum value for an INT, it's unlikely that changing to a BIGINT will solve the problem. It seems that you have a corrupt table, you're running into a bug in MySQL or whatever you're using to access it, or you're doing something wrong. Have you done a CHECK TABLE query? Can you show us the exact code you're using in the program that inserts the records, as well as the exact error message you're getting? Is it happening with every insert, or only some? For good measure, how about the output of SHOW CREATE TABLE table_name and SHOW TABLE STATUS LIKE 'table_name'? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: using php to connect to a 2nd mySQL server
the port is part of the server parameter in the connection eg port 3307 mysql_connect ( localhost:3307 , username , password ) http://www.php.net/manual/en/function.mysql-connect.php has the details HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net tel. 0121-242-1473 --- -Original Message- From: Webmaster [mailto:[EMAIL PROTECTED]] Sent: 09 October 2002 15:57 To: [EMAIL PROTECTED] Subject: using php to connect to a 2nd mySQL server Hello all, I am new to this list ... and hope to find an answer to a question which has come up. On the server for my websites at my ISP, a mySQL server is installed to handle different customers' databases (1 database setup for each customer) Now, as I have a need for a mySQL server where I can create/delete databases, my ISP agreed to install a 2nd mySQL server that can be dedicated to my sole use ... but I am told it would run on a different port number ... My question now is this: How can I tell a php script to not use the standard mySQL server that exists on this server computer, but to use the 2nd mySQL server that runs on the different port number ? Currently I use info set up in variables like this: # MySQL database config stuff $host=localhost; $username=abcdefg; $database=database; $password=123456789; Is it possible to add the port information somwhere? or does a php connection always use the standard mySQL port settings? Any help is greatly appreciated. Thanks! God bless you with His grace and peace Wolfgang Schneider -- BibelCenter: http://www.bibelcenter.de/ - Come and See! -- Bookstore: http://www.worthy.net/BibelCenter/ - Great offers! Check it out! -- email: mailto:[EMAIL PROTECTED] -- http://awesome.crossdaily.com/vote.php3?sid=952 -- Your Vote! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question about solving a query !
Is this what you're looking for? mysql select city,name,max(numbeds) from hospitals group by city; +---+--+--+ | city | name | max(numbeds) | +---+--+--+ | Lyon | Tonkin | 300 | | Marseille | Clairval | 150 | | Paris | AAA | 1500 | +---+--+--+ 3 rows in set (0.00 sec) Cheers, Andrew Sql,query -Original Message- From: Myoung-Ah KANG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 09 October 2002 16:45 To: [EMAIL PROTECTED] Subject: Question about solving a query ! Hello, I have a problem to treat a query with MySQL because MySQL does not permit nested SELECT clauses. The schema of the table is : Hospital num | name | city| numbeds 10 | Clairval| Marseille | 70 20 | Notre-Dame | Marseille | 150 30 | Tonkin | Lyon | 90 40 | Charpennes | Lyon | 300 80 | AAA| Paris |1500 90 | BBB | Paris |1400 100| CCC| Paris | 300 The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . In fact, I can write (if I do not use MySQL): Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); But it does not work with MySQL. I'm looking for a MySQL version of this query, I will be very grateful if you help me !!! Thank you !! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: using php to connect to a 2nd mySQL server
Hello Wolfgang, [..] My question now is this: How can I tell a php script to not use the standard mySQL server that exists on this server computer, but to use the 2nd mySQL server that runs on the different port number ? Currently I use info set up in variables like this: # MySQL database config stuff $host=localhost; $username=abcdefg; $database=database; $password=123456789; In your case the lines will look something like this if we asume that the host is 192.168.10.1 and the port 3307: $host=192.168.10.1:3307; $username=abcdefg; $database=database; $password=123456789; Best regards Daniel Khan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
fetching specific date from datetime field
Hi All My mysql database is set up having a field cdate - cdate datetime has the format -00-00 00:00:00 If I want to fetch rows for say the date 2000-12-19, I do it using perl (it's a tediuos code) Is there a way to do it using sql queries Also how can I get betwee two specific date that I may mention. Thanks Sincerely Aman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHAR vs. VARCHAR
From: christophe barbe [EMAIL PROTECTED]
Is it then correct that VARCHAR(255) and TINYTEXT are strictly
equivalent?
I believe they work almost the same, but there may be performance implications because
they are stored differently.
My understanding is that TINYTEXT is simply a sort of BLOB with FULLTEXT indexing
capability. BLOBs are stored separate from the main record, thus an additional
re-direction is involved, which may involve moving the disk heads.
Thus, BLOBs (and TEXT) cannot be properly indexed (although TEXT can have FULLTEXT
indeces, which are more complicated than normal indeces), and thus cannot be key
fields.
I don't know this from looking at the source code or MySQL docs, just from experience
with other databases and a quick look at what is enabled via phpMyAdmin. Feel free to
correct me!
Also, I think something else written in this thread is wrong. Someone said that CHAR
is much quicker than VARCHAR when traversing records, because the VARCHAR field has to
be searched for the end of the string. AFAIK, it only has to look at the size field to
determine how to skip to the next field, which is MUCH faster than searching a string
for a terminator. This has an additional implication that VARCHAR fields are always
bigger for a given string than an appropriately sized CHAR field.
Again, corrections welcome, since this is stuff I learned from deep ponderings while
optimizing Sybase and Informix databases.
NOTE: I understand now the sql,query stuff. Strange idea.
Thanks. I needed that. :-)
Thanks to the admins for adding it, too. Spam can be a pain, but due to other
thoughtful mail I receive, I've already re-financed my house, have low long distance
rates, are on first-name basis with all the hottest {appropriate adjective here}
chicks, and have huge breasts and penis, so I have no need of further such goods and
services offered via this list... :-)
--
: Jan Steinman -- nature photography: http://www.Bytesmiths.com
: Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services
: Join the forums at http://www.Bytesmiths.com/wiki
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: storing PDF files in mysql ?????
From: Sqlcoders.com Programming Dept [EMAIL PROTECTED] You should look into (via google.com) some doc to pdf converters. An example is this com object: http://www.convertzone.com/doc2pdfcom/help.htm#HowtoBuy, it costs $219 and converts .doc to .pdf. That's $219 saved if you're on MacOS X, since PDF conversion is universally available to any application that can print. Once you have the document in PDF, it's a relatively trivial action to store the documents in your MySQL database. Using BLOBs, I assume? I believe the original quester also wanted to be able to search them, which I believe is non-trivial! You can't simply slap a FULLTEXT index on them, right? It seems to me one would have to create an index BEFORE putting the PDFs in MySQL, then add that index as a separate FULLTEXT field. SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature photography: http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Join the forums at http://www.Bytesmiths.com/wiki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDb data file path
Scott Pippin wrote: I am trying to set up two data files in case the first one fills up. I tried to use the following in my.cnf but it says there is an error. If I take out the reference to the second data file everything works AIX 4.3.3 MySQL 4.0.4 innodb_data_file_path=libdata1:100M:autoextend:max:2000M;libdata2:100M:autoextend:max:2000M Any help would be greatly appreciated. (query, Mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Scott, From the innodb manual. It looks like you can only autoextend the last datafile listed. Starting from versions 3.23.50 and 4.0.2 InnoDB allows the last data file on the innodb_data_file_path line to be specified as auto-extending. The syntax for innodb_data_file_path is then the following: pathtodatafile:sizespecification;pathtodatafile:sizespec;... ...;pathtodatafile:sizespec[:autoextend[:max:sizespecification]] If you specify the last data file with the autoextend option, InnoDB will extend the last data file if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend instructs InnoDB to create just a single data file whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Displaying Default Values
On 9 Oct 2002, at 13:14, Lawrence S. Stephens III wrote: When I INSERT a record, that field is empty so that the default value is inserted into the field automatically (at least that's what I think). If your query is setting the column to the empty string, then you're setting it to the empty string, not the default value. To get the default value (before MySQL 4.0.3), you have to omit that column from the INSERT statement: http://www.mysql.com/doc/en/INSERT.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimizer bug in selecting fields that dont belong to the index used by mysql/Innodb
Rafa, it looks like the tuning I did to 3.23.53 fixes the optimization also in the 'albaranes' case below. Lenz starts building of 3.23.53 this week. With good luck 3.23.53 is available around Oct 17, 2002. Best regards, Heikki Innobase Oy heikki@hundin:~/mysql/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.53-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select count(*) from albaranes; +--+ | count(*) | +--+ |46100 | +--+ 1 row in set (0.59 sec) mysql explain select * from albaranes where alb 'SA' limit 10 - ; +---+---+---+-+-+--+---+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+-+-+--+---+ + | albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 | where u sed | +---+---+---+-+-+--+---+ + 1 row in set (0.00 sec) mysql explain select * from albaranes where alb 'BA' limit 10 - ; +---+---+---+-+-+--+---+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+-+-+--+---+ + | albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 | where u sed | +---+---+---+-+-+--+---+ + 1 row in set (0.00 sec) mysql explain select * from albaranes where alb 'MA' limit 10 - ; +---+---+---+-+-+--+---+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+-+-+--+---+ + | albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 | where u sed | +---+---+---+-+-+--+---+ + 1 row in set (0.00 sec) mysql explain select * from albaranes where alb 'SB' limit 10 - ; +---+---+---+-+-+--+---+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+-+-+--+---+ + | albaranes | range | PRIMARY | PRIMARY | 8 | NULL | 44491 | where u sed | +---+---+---+-+-+--+---+ + 1 row in set (0.00 sec) - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, October 09, 2002 1:17 PM Subject: optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb Description: Hello, We reported a problem about selecting fields that some of them didn´t belong to the used index on September. We have already installed MySql 4.0.4 and we have a similar problem (and also we are still having the original problem) when we select fields which some of them don´t belong to the primary index that MySql should use in the search. In this query, we haven´t any OR condition in the WHERE clause as we had in the query that we made on September. Moreover, in this case MySQL doesn´t use any index when runs the query. So, we think this problem is bigger than the previous one. We have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (albaranes.txt) in a compressed file named Albaranes.zip so you can reproduce the bug. We hope the test we are going to explain can help you to fix the bug. Now, the table definition is (we have got 45055 records) CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', `Fac` varchar(8) NOT NULL default '', `Ped` varchar(8) NOT NULL default '', `Pis` char(2) NOT NULL default '', `Fec` date default NULL, `Dom` char(2) NOT NULL default '', `FoP` char(2) NOT NULL default '', `Ven` char(3) NOT NULL default '', `Rep` char(3) NOT NULL default '', `Ba1` decimal(20,4) NOT NULL default '0.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `Iv1` float NOT NULL default '0', `Iv2` float NOT NULL default '0', `Iv3` float NOT NULL default '0', `Iv4` float NOT NULL default '0', `Re1` float NOT NULL default '0', `Re2` float NOT NULL default '0', `Re3` float NOT NULL default '0', `Re4` float NOT NULL default '0', `Dps` decimal(20,4) NOT NULL default '0.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.',
Re: fetching specific date from datetime field
select * from table where substring(timestampField,1,8) = 'MMDD' from Perl, execute this statement and then process each returned row - hcir My mysql database is set up having a field cdate - cdate datetime has the format -00-00 00:00:00 If I want to fetch rows for say the date 2000-12-19, I do it using perl (it's a tediuos code) Is there a way to do it using sql queries Also how can I get betwee two specific date that I may mention. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Question about solving a query !
Here is a correct one to solve your problem: select b.city, b.name, b.numbeds, count(distinct a.numbeds) c from hospitals a, hospitals b where a.city = b.city and a.numbeds=b.numbeds group by b.name, b.city, b.numbeds having c=1; -Original Message- From: Myoung-Ah KANG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 8:45 AM To: [EMAIL PROTECTED] Subject: Question about solving a query ! Hello, I have a problem to treat a query with MySQL because MySQL does not permit nested SELECT clauses. The schema of the table is : Hospital num | name | city| numbeds 10 | Clairval| Marseille | 70 20 | Notre-Dame | Marseille | 150 30 | Tonkin | Lyon | 90 40 | Charpennes | Lyon | 300 80 | AAA| Paris |1500 90 | BBB | Paris |1400 100| CCC| Paris | 300 The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . In fact, I can write (if I do not use MySQL): Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); But it does not work with MySQL. I'm looking for a MySQL version of this query, I will be very grateful if you help me !!! Thank you !! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NULL sometimes joins to NULL
Description: After adding a key to a nullable column, null values will successfully join through to null values in other tables. This only seems to happen when the index is added after the row contains null values. This affects both MyISAM and InnoDB table types and both binary versions 3.23.42 and 4.0.4. How-To-Repeat: Run the following queries. The first select will return 0 rows, which is correct. The second select will return a match - but all that was changed was the adding of an index. mysql create table foo (id int); Query OK, 0 rows affected (0.01 sec) mysql insert into foo values (null), (0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql create table bar (id int); Query OK, 0 rows affected (0.00 sec) mysql insert into bar values (null); Query OK, 1 row affected (0.00 sec) mysql select * from foo, bar where foo.id = bar.id; Empty set (0.00 sec) mysql alter table foo add key id (id); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from foo, bar where foo.id = bar.id; +--+--+ | id | id | +--+--+ | NULL | NULL | +--+--+ 1 row in set (0.00 sec) mysql Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: adding key makes null columns join Severity: Priority: Category: mysql Class: Release: mysql-4.0.4-beta (Source distribution) Environment: System: Linux db2 2.4.18-64GB-SMP #1 SMP Wed Mar 27 13:58:12 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-O6 -mpentiumpro -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394238 Jul 12 08:29 /lib/libc.so.6 -rw-r--r--1 root root 25361424 Jul 12 06:58 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 2002 /usr/lib/libc.so Configure command: ./configure --prefix=/opt/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared -with-extra-charsets=none 'CFLAGS=-O6 -mpentiumpro -fomit-frame-pointer' 'CXXFLAGS=-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: drop constraint in innodb
Is there any other way to do this in a table with thousands of records. - Original Message - From: "Heo, Jungsu" [EMAIL PROTECTED] To: "kayamboo" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 8:50 PM Subject: Re: drop constraint in innodb Last week, I posted a message related to this. Heikki (InnoDB Developer) said "sorry, DROP CONSTRAINT is not implemented yet." - Original Message - From: "kayamboo" [EMAIL PROTECTED] To: "list mysql" [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 2:45 PM Subject: drop constraint in innodb sql, query Hi Folks ALTER TABLE main_db ADD CONSTRAINT FOREIGN KEY( Code, Kana) REFERENCES Syoyu_db( Code, Kana) ON DELETE CASCADE ; works fine. But how to drop the constraint or modify the constraint ? Simply changing ADD to DROP or MODIFY throws errors regards - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing PDF files in mysql ?????
Hello There are lots of discussions in this regard and the suggestions are always to save your blob somewhere in your hard disk and just store the path. In my case, each of my image is around 1MB but you know, I find it difficult to map the images stored in a particular machine, through a network using Delphi. But storing in the database solves most of the problems and the users can also easily load and retrieve them. But any suggestions from experts in this regard is welcome regards - Original Message - From: "Alexis Antonakis" [EMAIL PROTECTED] To: "list mysql" [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 8:57 PM Subject: RE: storing PDF files in mysql ? Hi, The thing I was wondering is whether there is any advantage in saving IMAGES in a database? I have had a little play around with a 50k jpeg file and it appears that if I save it in Blob format, then the filesize of the database increases by 50K as well, and surely converting it from/to a database just add time to the loading of a page, albeit it very minimal, but as I am designing a site which will host huge numbers of photos, this will make a big difference. Any thoughts or advice on this topic will be most appreciated. Regards Alexis -Original Message- From: kayamboo [mailto:[EMAIL PROTECTED]] Sent: 09 October 2002 01:20 To: toby z Cc: list mysql Subject: Re: storing PDF files in mysql ? I think if you decide to store in the blob, whatever it is, a pdf or jpeg or bmp, it is going to be in binary format. While you need to display it back, just convert them back to whatever format you like. Else there is always the choice of just storing the path in your database. By the way, which language you are using to write your applications. If it is delphi, I can send you some sample code regards - Original Message - From: "toby z" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 1:14 AM Subject: Re: storing PDF files in mysql ? thnx rich but u see the problem iz my clients want their text IN pdf format to be displayed with all these colors on the page in other words they want their sites layout to be maintained on the pages showing this pdf text as they are pretty concerned bout security so what do i do now thnx a mill toby From: "R.Dobson" [EMAIL PROTECTED] To: toby gibbson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: storing PDF files in mysql ? Date: Tue, 08 Oct 2002 16:35:56 +0100 hi, 2 ways to do this: a) don't store the file actually in the db but store the path to it. b)use the function LOAD_FILE(filename) to get the contents of a file as a string value and store as a blob. decide which to use depending on how many pics you have cheers Rich toby gibbson wrote: ok guyz more trouble now im supposed to convert all the text (from ms word) to PDF and put the pdf files in mysql db now i ve really got myslf stuck havent i can anyone plz hlp thnx a million stuck az usual toby . _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing PDF files in mysql ?????
Hi toby
Just replace the .gif with your .pdf. I hope this will work for you.
INSERT INTO photo(name , pic, picname) values( 'raman',
load_file('e:/mysql/images/Click.gif'),'Click.gif') ;
SELECT length(pic) FROM photo WHERE picname = 'Click.gif' ;
UPDATE photo SET pic = load_file('e:/mysql/images/mod_jk.jpeg') where name =
'velan' ;
SELECT pic FROM photo WHERE name = 'velan' INTO DUMPFILE 'C:/TEMP/pic2.gif'
Good Luck
- Original Message -
From: "toby z" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Wednesday, October 09, 2002 9:45 PM
Subject: RE: storing PDF files in mysql ?
thnx guyz
1) i m tryin to put the pdf file into a blob field but i cant insert it
like
normal text how shall i go about inserting it
2) i m in the process of converting the word doccuments to pdf got this
acrobat disteller n m havin a million probz with that alone like the
security issue
I NO THIS IS THE WRONG LIST SORRY FOR THIS Q BUT .. has anyone worked with
this thing n wouldnt mind me mailin him/her plz do lemme no
guyz plz hlp .
thnx a billion
toby
From: "Sqlcoders.com Programming Dept" [EMAIL PROTECTED]
To: "toby z" [EMAIL PROTECTED],"list mysql"
[EMAIL PROTECTED]
Subject: RE: storing PDF files in mysql ?
Date: Tue, 8 Oct 2002 21:47:23 -0600
Toby,
If I understand correctly you want to perform two distinct actions:
1. Convert Microsoft Office Word .doc files into .pdf
2. Store those pdf's in MySQL.
You should look into (via google.com) some doc to pdf converters. An
example
is this com object:
http://www.convertzone.com/doc2pdfcom/help.htm#HowtoBuy,
it costs $219 and converts .doc to .pdf.
If you can convert the .doc's into .html files, you might be able to get
away with using Adobe's free email converter service.
Email the HTML document to [EMAIL PROTECTED], wait an hour or so, and
you'll find a pdf version in your inbox.
(There's also a [EMAIL PROTECTED] address if you dont want to use html,
but
I think you wanted to keep formatting).
Once you have the document in PDF, it's a relatively trivial action to
store
the documents in your MySQL database.
HTH,
William.
-Original Message-
From: kayamboo [mailto:[EMAIL PROTECTED]]
Sent: 08 October 2002 18:20
To: toby z
Cc: list mysql
Subject: Re: storing PDF files in mysql ?
I think if you decide to store in the blob,
whatever it is, a pdf or jpeg or bmp, it is going to be in binary
format.
While you need to display it back, just convert them back to
whatever format
you like.
Else there is always the choice of just storing the path in your
database.
By the way, which language you are using to write your applications.
If it is delphi, I can send you some sample code
regards
- Original Message -
From: "toby z" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, October 09, 2002 1:14 AM
Subject: Re: storing PDF files in mysql ?
thnx rich
but u see the problem iz my clients want their text IN pdf format to
be
displayed with all these colors on the page in other words they
want
their sites layout to be maintained on the pages showing this pdf
text
as they are pretty concerned bout security
so what do i do now
thnx a mill
toby
From: "R.Dobson" [EMAIL PROTECTED]
To: toby gibbson [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: storing PDF files in mysql ?
Date: Tue, 08 Oct 2002 16:35:56 +0100
hi,
2 ways to do this:
a) don't store the file actually in the db but store the path to
it.
b)use the function LOAD_FILE(filename) to get the contents of
a file as a
string value and store as a blob.
decide which to use depending on how many pics you have
cheers
Rich
toby gibbson wrote:
ok guyz more trouble
now im supposed to convert all the text (from ms word) to PDF and
put
the
pdf files in mysql db
now i ve really got myslf stuck havent i
can anyone plz hlp
thnx a million
stuck az usual
toby .
_
Chat with friends online, try MSN Messenger:
http://messenger.msn.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
--
Richard Dobson
Database Administrator
MRC Bright Study
Re: drop constraint in innodb
HI If suppose my table has thousands of records, is it possible to create a new table without a foreign key constraint, and then copy the existing data with the foreign key constraint to the new one ? regards - Original Message - From: "Egor Egorov" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 09, 2002 9:15 PM Subject: re: drop constraint in innodb kayamboo, Wednesday, October 09, 2002, 8:45:39 AM, you wrote: k ALTER TABLE main_db ADD CONSTRAINT FOREIGN KEY( Code, Kana) REFERENCES k Syoyu_db( Code, Kana) ON DELETE CASCADE ; k works fine. k But how to drop the constraint or modify the constraint ? k Simply changing ADD to DROP or MODIFY throws errors Curretly you can't drop only foreign key constarints. You should re-create a table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
password / last record
sql, query Hello Folks 1. Is there anyway to know the string value of the password using the encrypted value , from the mysql.user table ? 2. How can I know the most recently entered record, that does not have an AUTOINCREMENT column ? Thanks for the tips. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql@lists.mysql.com
mysqlÄúºÃ! Ìì½òÊб±´óҽԺǿֱ¿Æ¼Ì³ÐÖйú´«Í³Ò½Ñ§µÄ¾«Ë裬·¢¾ò³öÁËÇ峯Ãñ¼äÌØЧ ÖÎÁÆÇ¿Ö±ÐÔ¼¹ÖùÑ×µÄÎâÊÏÖÐÒ½ÁÆ·¨£¬ÓÐЧÖÎÁÆÇ¿Ö±¼¹ÖùÑס£ ¸ÃÁÆ·¨²ÉÓöÀÌصĽ«Ö²¡Òò×ӰγöÌåÍâµÄ·½·¨,Ó¦ÓÃÏÊÖвÝÒ©Íâ·óÖÎÁÆ£¬ÎÞ Ðè¿Ú·þ£¬ÎÞ¶¾¸¶×÷ÓÃ, ¸ü²»»áÉ˼°³¦Î¸, Ò»°ãÇé¿öÖ»ÐèÁ½¸öÁƳÌ15Ìì×óÓÒ¼´¿É ʹ»¼Õß½â³ý²¡Í´¡£ ¾Öڶ໼ÕßÁÙ´²Ê¹Óã¬Ö¤Êµ¸ÃÁÆ·¨¶ÔÇ¿Ö±ÐÔ¼¹ÖùÑ×µÄÓÐЧÂÊ¿É´ï90%ÒÔÉÏ£¬ ÖÎÁƺ󸴷¢Âʼ«µÍ¡£ÄÜ¿ìËÙÓÐЧֹʹ¡£ »¶Ó»¼ÕßÀ´ÐÅÌáÎÊ£¬24СʱÄڵõ½×¨Òµ×Éѯ¡£ ÏêÇé²ÎÔÄÍøÕ¾£ºhttp://www.xfzg.com 24Сʱ×Éѯµç»°£º 022-27276811 022-27278031´ÞÖ÷ÈÎ ´ËÐÅÈç¶ÔÄúÉú»îÔì³É¸ÉÈÅÇëÔÁ£¬Ë³ÊÖɾ³ý¡£ Ö Àñ! Ìì½òÊб±´óÒ½Ôº 2002.8 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with Sum(), newbie
OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: information about the tables in the database
On Wednesday 09 October 2002 21:43, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db Talking about programmatically? What client are you using? Since the JDBC driver can pick up all the database, table and column information, it should be available reasonably easy in other clients as well. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
By mistake I removed root server of localhost thru webmin
Dear List, I have removed root user of localhost thru webmin and now i am not able to login into MySQL Interface of webmin as it says Incorrect user name and password when I try to enter as any user [root or any XYZ user], please tell me how to create this user for MySQL so that I can access this interface again. Please note that I am able to use root login for all other purposes but Webmin does not allow to enter into MySQL Interface only. Regards, Vivek - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with Sum(), newbie
well the result is correct. the sum of first is 1 for the first record. the sum of first AND second would be 3. select first, second, sum(first + second) from example; is that what you want? Rgds Terence - Original Message - From: Kevin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 10, 2002 10:36 AM Subject: Help with Sum(), newbie OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help with Sum(), newbie
snip mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? snip If you would like the result of the query to be the sums of the columns try this: SELECT SUM(first), SUM(second) FROM example; Or alternatively, just the sum from the first column use SELECT SUM(first) FROM example; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: password / last record
Hi, On Thu, 2002-10-10 at 10:57, kayamboo wrote: 1. Is there anyway to know the string value of the password using the encrypted value , from the mysql.user table ? No. It's scrambled with a one-way (lossy) algorithm. 2. How can I know the most recently entered record, that does not have an AUTOINCREMENT column ? You can't. Rows are by definition un-ordered in SQL databases, because storage is an internal matter for the server, it can do it any way it sees fit (it'll depend on the format, and other considerations like filling gaps of deleted rows, etc). The only logical ordering is one you put in (with AUTO_INCREMENT or timestamps or whatever). Actually you would only see that order if you use SELECT ... ORDER BY ... Otherwise, output is also unordered! (the fact that it may appear ordered while not using ORDER BY is purely coincidence: delete a row and insert a new one and you'll definitely find more disorder ;-) Regards, Arjen. -- MySQL Training in Auckland and Sydney, http://www.mysql.com/training/ Purchase Training, Support, Licenses @ https://order.mysql.com/?marl __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDb data file path
Hi Scott, On Thu, 2002-10-10 at 03:28, Scott Pippin wrote: I am trying to set up two data files in case the first one fills up. I tried to use the following in my.cnf but it says there is an error. If I take out the reference to the second data file everything works AIX 4.3.3 MySQL 4.0.4 innodb_data_file_path=libdata1:100M:autoextend:max:2000M;libdata2:100M:autoextend:max:2000M Only one (generally the last) data file may be autoextend. Regards, Arjen. -- MySQL Training in Auckland and Sydney, http://www.mysql.com/training/ Purchase Training, Support, Licenses @ https://order.mysql.com/?marl __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Maximize Performance by Improving Search Program Logic - Help?
Hi,
I need to improve the performance of a 1-word search engine
which I've created. Currently the RAM is 1 GIG and the key_buffer is set at
512 MB which has improved the search results but I think perhaps I can improve
my method of search with your help perhaps. The program is coded to run in
mod_perl or just perl.
It consists of 2 tables: word and content.
content consists of 2 fields:
rec_id int unsigned not null,
description varchar(200) not null
word consists of 2 fields:
word varchar(50) not null,
rec_id int unsigned not null
word is created from content. Each actual word from the field
content.description is created as a record in the table word with its rec_id.
Both tables are indexed on rec_id and word respectively.
A keyword is entered such as book.
The search consists of 2 SQL queries
Query 1: select rec_id from word where word='book';
All the rec_ids are captured into an array.
Query2: select description from content where rec_id=?
I then loop over the rec_ids stored in the array @codes.
my $SqlCmd = 'select description from content where rec_id=?';
my $sth = $dbh-prepare( $SqlCmd );
$sth-{'mysql_use_result'} = 1; # doesn't store entire result
while ( $continue ) {
if ($njk $#codes) { last; }
$cd = $codes[$njk++];
$sth-execute($cd);
@row = $sth-fetchrow_array;
# Collect results for display etc ...
}
$sth-finish;
Test Results:
Query 1 Query 2
pottery (915 records) 0 sec 1 sec
book (12456 records)0 sec13 sec
computer (21999 records)1 sec21 sec
Question: Is there a better way of doing this with respect to:
A. My definition of fields and their types.
B. My queries - it seems that Query 2 could perhaps be made
faster.
I thank you in advance. It is my hope to increase to 2, 3 and 4 word searches
once I've maximized the above search logic.
regards
David Jourard
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
resetting mysql server gently
Hi, I have a mysql + php + apache server that is under heavy load. I have a script that runs every minute to deterimine if the load is above a certain value (or if swap is high) and if so, it kills all the httpd processes, does /etc/rc.d/init.d/mysql stop The script then starts everything back up again. I am curious if there is a better way to restart mysql that would kill off any hung/long-running queries but not totally bring the server down. Any other ideas for managing a big load. Bottom line - killing off httpd is not too bad since users see a temporary glitch, but killing mysql seems to give a lot problems - scripts don't like to have the mysql server suddenly go away. Is there a gentler way that I can reset mysql? Thanks for any input, ideas, etc! __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
./configure --withorwithout-mit-pthreads
I switched my server from linux debian to NetBSD 1.6. All when fine until I tried to do ./configure for the mysql-3.23.52 from the source tar.gz. Problem is, it keeps looping saying can't cd to mit-pthreads. I did my homework and seeked a solution on mailing list, google etc and got nothing good. I installed mit-pthread 1.60b6 from the 1.6 packages collection of netbsd without any success, it's still whinning about not being able to change directory mit-pthreadsI tried ./configure --without-mit-pthread but even if in the docs it says linux need linux threads and others like freebsd and solaris need maybe mit-pthreads I thought I could get around without it (not logic but hey, I tried). Then lot's of bad stuff is said about using mit pthreads with mysql... I haven't quite completely understood what were the differences but... Can you tell me if there's a patch something I could do to compile it anyway and get to use it with the stability it's supposed to have when compiled without problems etc... (oh btw, I tried the beta 4.0.4 version... not working either). It is telling me the same thing on and on and on (./configure will never stop) It restart from the beginning over and over... until a deadly kill -9 or whatever. Have a pleasant day, hope to get feedbacks soon! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
