Re: is your lack of a degree holding you back?
Simon Thanks for your note. It sounds like you're having an interesting time. We're having a lot of fun with NimbusDB. It is one of those revolutionary architectural re-thinks that changes an industry. No-one else has worked out how to build an elastically scalable JDBC/SQL/transactional database. We're not talking about it openly yet, but sign up for the mailing list athttp://www.nimbusdb.com if you want more tech detail. As relates to your request, give me a call. I think the timing will work. Regards Barry On 2/12/11 4:46 AM, USAFlorist.com wrote: Dear friend, friend (instantdegree...@gmail.com) has sent you the following link at USAFlorist.com: http://www.usaflorist.com/products/1-800-flowers_fun__flirty.htm?refcode=1A2 Your Friend's Message to you: Do you have the knowledge and the experience in your field but lack the qualifications? Are you getting turned down time and time again for the job of your dreams because you just don't have the right letters after your name? Get the prestige that you deserve and move ahead in your career today! Bachelors, Masters, MBAs, and PhDs available in your field! No examinations, classes, or textbooks if you qualify! Contact us to register and receive your qualifications in as little as two weeks! 24 hours a day 7 days a week! Confidentiality assured! Contact us at: instantdegr...@linuxmail.org Just leave your NAME CONTACT INFO in the email. Our staff will get back to you in the next few days.
Re: is your lack of a degree holding you back?
Apologies - pls ignore my email below. On 2/12/11 1:01 PM, Barry Morris wrote: Simon Thanks for your note. It sounds like you're having an interesting time. We're having a lot of fun with NimbusDB. It is one of those revolutionary architectural re-thinks that changes an industry. No-one else has worked out how to build an elastically scalable JDBC/SQL/transactional database. We're not talking about it openly yet, but sign up for the mailing list athttp://www.nimbusdb.com if you want more tech detail. As relates to your request, give me a call. I think the timing will work. Regards Barry On 2/12/11 4:46 AM, USAFlorist.com wrote: Dear friend, friend (instantdegree...@gmail.com) has sent you the following link at USAFlorist.com: http://www.usaflorist.com/products/1-800-flowers_fun__flirty.htm?refcode=1A2 Your Friend's Message to you: Do you have the knowledge and the experience in your field but lack the qualifications? Are you getting turned down time and time again for the job of your dreams because you just don't have the right letters after your name? Get the prestige that you deserve and move ahead in your career today! Bachelors, Masters, MBAs, and PhDs available in your field! No examinations, classes, or textbooks if you qualify! Contact us to register and receive your qualifications in as little as two weeks! 24 hours a day 7 days a week! Confidentiality assured! Contact us at: instantdegr...@linuxmail.org Just leave your NAME CONTACT INFO in the email. Our staff will get back to you in the next few days.
Re: mysqldump hex-blob option
On 3/4/10 7:21 PM, peng yao xwei...@gmail.com wrote: hello erveryone, I have a question about mysqldump.I have some blob data, someone tell me mysqldump the data must use hex-blob options, why? mysqldump just creates a file containing insert statements that when executed rebuild your tables. As a result, this file cannot contain binary data and so BLOBs must be converted to hex. If you know your BLOBs are just text then you can get away with out the hex-blob option. Barry Leslie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] Beta release v05.011 of the BLOB Streaming Daemon
Hi, Beta version 5.011 of the BLOB streaming daemon for MySQL has been released. The BLOB Streaming Daemon is a MySQL engine that runs as a daemon enabling the storage and streaming of BLOB data directly in and out of a MySQL database. You can download the source code from http://www.blobstreaming.org/download. For more details see: http://www.blobstreaming.org/documentation or visit my BLOG at http://bpbdev.blogspot.com Some of the new features in 5.011: - S3 storage of BLOBs is now supported. - Backup will backup BLOBs stored in S3 on the S3 server itself. - The PBMS enabled MySQL java connector has been updated and is now working again. - The PrimeBase Media Streaming web site has been updated and the documentation has been made more user friendly. If you have any questions or comments feel free send them directly to me. Barry - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
Hi, The problem of BLOB storage with MySQL is not the actual storage of the BLOB data, it is the getting BLOBs to and from the client. Traditionally MySQL treats the BLOB data as any other data and as a result there can be major memory usage and performance issues as the BLOB is passed back to the client app. This is why no matter how clever the storage engine is in how it stores the BLOB, the main problem remains. That is why the BLOB streaming daemon was developed because it allows the actual BLOB data to be streamed to and from the database server outside of the normal MySQL client/server connection. So you can have the best of both worlds: - Similar to storing the BLOB in the file system: BLOB references are stored with the normal table data, not the actual BLOB data, so the tables do not get bloated, and because the BLOB data is accessed through the BLOB streaming daemon it has little impact on server performance and memory requirements are not dependent on BLOB size. - Similar to traditional storage of BLOBs in tables: the BLOB will automatically be delete when the row referencing it is deleted and the application designer doesn't need to implement their own file system based BLOB storage. So if you are looking at BLOB storage systems please have a look at http://www.blobstreaming.org or the launch pad project: https://launchpad.net/pbms (not pbxt as Paul mentioned.) Sorry if this is a bit of a plug for PBMS but the subject was BLOB storage and that is PBMS's sole purpose in life. Barry On 2/16/10 8:21 AM, Martin Gainty mgai...@hotmail.com wrote: i agree with jerry put date/timestamps on each record..(that way you know when the record was created/modified) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: jschwa...@the-infoshop.com To: vikkiatb...@yahoo.in; vegiv...@tuxera.be CC: mysql@lists.mysql.com Subject: RE: how things get messed up Date: Tue, 16 Feb 2010 11:02:22 -0500 -Original Message- From: Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Friday, February 12, 2010 4:13 AM To: Johan De Meersman Cc: MY SQL Mailing list Subject: Re: how things get messed up Sir, Thanks for your suggestion, I will go for blob storage, because our application will maintain the data on yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not face such kind of performance issue in our application. [JS] It sounds like you are planning to have one table per year. Regardless of where you put your blobs, I think that is a bad idea from a design standpoint. It will make it harder to find historical information. If your database is relatively small, then I'd just keep everything in one table. If it is big, then roll data that is five years old into an archive table. That will give you only two places, and an easy-to-follow rule to tell you where to look. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail: Powerful Free email with security by Microsoft. http://clk.atdmt.com/GBL/go/201469230/direct/01/ - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives
[ANN] Alpha release v05.09 of the BLOB Streaming Engine
Hi, Alpha version 5.09 of the BLOB streaming engine for MySQL has been released. The BLOB Streaming Engine is a MySQL storage engine which enables the storage and streaming of BLOB data directly in and out of a MySQL database. You can download the source code from http://www.blobstreaming.org/download. For more details see: http://www.blobstreaming.org/documentation Some of the new features in 5.08: * Engine level backup and restore of BLOB data. * PBMS now supports transactions. * A new PHP extension for PBMS. * A simplified interface between PBMS and other storage engines. For a full list of changes please have a look at my BLOG posting http://bpbdev.blogspot.com/ If you have any questions or comments feel free send them directly to me. Barry - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Uploading large files with mySQL
Hi Dan, The problem with BLOB is that traditionally MySQL, along with a lot of other databases, is not designed to handle them very well. The BLOB data is passed between the client and server as if it where the same as any other data. This results in large memory use by both the client and server as the BLOB is buffered on both sides. The standard solution to this was to store the BLOBs some where in a file system and then place some form of reference to the BLOB in the database that could then be used by the client to get the actual data. The problem of how to set such a system up and maintain the externally stored data was left up to the individual application designer. The good news is that I am working on a generic solution to this problem called the PrimeBase Media Stream engine (PBMS) that is intended to handle exactly what you want to do. PBMS is a specialized storage engine that works with other storage engines to store BLOB data. The actual BLOB data is streamed to and from the PBMS engine itself and is not passed through the MySQL server and client interface. What is stored in the actual BLOB columns in the normal storage engine tables is a BLOB reference that can be used to get the real BLOB data from the PBMS engine. The PBMS engine handles the storage of the BLOB data which may be stored locally or could be stored remotely in Amazon S3 storage for example. For more information please have a look at our web site: http://www.blobstreaming.org or check out my BLOG http://bpbdev.blogspot.com. Good luck on your project. Barry On 7/13/09 5:08 AM, Daniele Development-ML daniele@googlemail.com wrote: Hello, I'm developing a web application that requires to store large files in a MySQL database. The files can range up to 2Gb. In my understanding the upper limit for the SQL queries (thus insert queries) is 1GB. I'm trying, for the time being, to upload files of sizes around 65MB, but I still get some errors - including the MySQL server has gone away. I followed the discussion in other post, and I properly set all the system variables (max_allowed_packet_size, wait_timeout) to the maximum value, but the error still occurs. Would you suggest looking at something in particular? For the time being, just to try this, I'm inserting the file through the MySQL Query Browser - just not to add other possible sources of problems on top of that. Thanks! Dan - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] Alpha release v05.08 of the BLOB Streaming Engine
Hi, Alpha version 5.08 of the BLOB streaming engine for MySQL has been released. The BLOB Streaming Engine is a MySQL storage engine which enables the storage and streaming of BLOB data directly in and out of a MySQL database. You can download the source code from http://www.blobstreaming.org/download. For more details see: http://www.blobstreaming.org/documentation Some of the new features in 5.08: * It is now possible to store user defined metadata with the BLOB. * It is now possible to assign an alias to the BLOB and then use the alias to fetch the BLOB back from the engine. For a full list of changes please have a look at my BLOG posting http://bpbdev.blogspot.com/ If you have any questions or comments feel free send them directly to me. Barry - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] Alpha release v05.06 of the BLOB Streaming Engine
Hi, Alpha version 5.06 of the BLOB streaming engine for MySQL has been released. The BLOB Streaming Engine is a MySQL storage engine which enables the storage and streaming of BLOB data directly in and out of a MySQL database. You can download the source code from http://www.blobstreaming.org/download. Documentation has also been updated . For more details see: http://www.blobstreaming.org/documentation What's new in 5.06: * The big news is that as of this release the BLOB streaming engine can be used with tables from any MySQL storage engine. * A name change has taken place: the PrimeBase BLOB streaming engine's name has been changed from MyBS to PBMS which stands for PrimeBase Media Streaming. If you have any questions or comments feel free send them directly to me. Barry - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: [EMAIL PROTECTED] Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] Alpha release v05.05 of the BLOB Streaming Engine
Hi, Alpha version 5.05 of the BLOB streaming engine for MySQL has been released. You can download the source code from http://www.blobstreaming.org/download. Documentation has also been updated . The BLOB Streaming Engine is a MySQL storage engine which enables the storage and streaming of BLOB data in and out of a MySQL database. For more details see: http://www.blobstreaming.org/documentation What's new in 5.05: * A 'C' API has been added for client applications. It provides all the basic functions needed to connect to the BLOB streaming engine and upload and download BLOBs efficiently. * A test client application has been added to the project to demonstrate the use of the new API. * Added discover table support for the engine's system tables. * Simplified the configuration: To configure the engine all you have to do is provide the path to the MySQL source tree (after building MySQL). All build options are taken from the MySQL build. * And of course there are assorted bug fixes, details of which are listed in the Changelog. Of special interest to other engine developers may be the way that I implemented 'discover tables'. I have created a very generic function that takes a structure similar to that used by schema plug-ins and generates the required 'frm' file. Implementation is contained in the file BSDiscover.cc and you can see how it is used in ha_mybs.cc. - Barry Leslie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Confusion!
There are only 500 records in total of which three are relevant to the 'plus' query. But there is only 1 relevant result from the 'real' query, and that did return a result. Brent Baisley wrote: Is the plus query return more then 50% of the records? If so, MySQL won't return anything since the result set isn't that relevant. Brent Baisley Systems Architect On Apr 11, 2008, at 8:08 AM, Barry wrote: I am confused ( nothing new there), what I thought was a simple search is proving not to be so, Can anyone tell me why this query for the word 'plus': mysql SELECT * - FROM booktitles - WHERE MATCH (category , publisher , bookTitle , author) - AGAINST (CONVERT( _utf8'plus'USING latin1 ) - IN BOOLEAN MODE) - ORDER BY category; Empty set (0.00 sec) returns an empty result set, when this query: mysql SELECT * - FROM `booklist`.`booktitles` - WHERE `id` LIKE '%plus%' - OR `category` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `publisher` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `bookTitle` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `author` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `publishDate` LIKE '%plus%'; +-+--+---+---+-+-+ | id | category | publisher | bookTitle | author | publishDate | +-+--+---+---+-+-+ | 39 | C++ | SAMS | C++ Primer Plus Fourth Edition | Stephen Prata | 2001 | | 162 | Linux | Wiley | Ubuntu Linux Toolbox 1000 plus Commands for Ubuntu and Debian Power Users | Christopher Negus Fran�ois Caen | 2007 | | 496 | C++ | Prentice Hall | C Plus Plus GUI Programming With Qt 4 2nd Edition | Jasmin Blanchette, Mark Summerfield | 2008 | +-+--+---+---+-+-+ 3 rows in set (0.00 sec) provides the correct answer? Thinking that it the first query wasn't picking up a four letter term, I ran this search for the word 'real' mysql SELECT * - FROM booktitles - WHERE MATCH (category , publisher , bookTitle , author) - AGAINST (CONVERT( _utf8'real'USING latin1 ) - IN BOOLEAN MODE) - ORDER BY category; +-+--+---+-+--+-+ | id | category | publisher | bookTitle | author | publishDate | +-+--+---+-+--+-+ | 134 | Linux | Prentice Hall | Embedded Linux Primer: A Practical, Real-World Approach | Christopher Hallinan | 2006 | +-+--+---+-+--+-+ 1 row in set (0.00 sec) and as you can see it came up with the correct result. Thanks for looking Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Confusion!
I am confused ( nothing new there), what I thought was a simple search is proving not to be so, Can anyone tell me why this query for the word 'plus': mysql SELECT * - FROM booktitles - WHERE MATCH (category , publisher , bookTitle , author) - AGAINST (CONVERT( _utf8'plus'USING latin1 ) - IN BOOLEAN MODE) - ORDER BY category; Empty set (0.00 sec) returns an empty result set, when this query: mysql SELECT * - FROM `booklist`.`booktitles` - WHERE `id` LIKE '%plus%' - OR `category` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `publisher` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `bookTitle` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `author` LIKE CONVERT( _utf8 '%plus%' - USING latin1 ) - COLLATE latin1_swedish_ci - OR `publishDate` LIKE '%plus%'; +-+--+---+---+-+-+ | id | category | publisher | bookTitle | author | publishDate | +-+--+---+---+-+-+ | 39 | C++ | SAMS | C++ Primer Plus Fourth Edition | Stephen Prata | 2001 | | 162 | Linux | Wiley | Ubuntu Linux Toolbox 1000 plus Commands for Ubuntu and Debian Power Users | Christopher Negus Fran�ois Caen | 2007 | | 496 | C++ | Prentice Hall | C Plus Plus GUI Programming With Qt 4 2nd Edition | Jasmin Blanchette, Mark Summerfield | 2008 | +-+--+---+---+-+-+ 3 rows in set (0.00 sec) provides the correct answer? Thinking that it the first query wasn't picking up a four letter term, I ran this search for the word 'real' mysql SELECT * - FROM booktitles - WHERE MATCH (category , publisher , bookTitle , author) - AGAINST (CONVERT( _utf8'real'USING latin1 ) - IN BOOLEAN MODE) - ORDER BY category; +-+--+---+-+--+-+ | id | category | publisher | bookTitle | author | publishDate | +-+--+---+-+--+-+ | 134 | Linux | Prentice Hall | Embedded Linux Primer: A Practical, Real-World Approach | Christopher Hallinan | 2006 | +-+--+---+-+--+-+ 1 row in set (0.00 sec) and as you can see it came up with the correct result. Thanks for looking Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure problem
Hello everyone! Hopefully somone can enlight me, i am hanging on this vor a few hours now :/ i have this stored procedure: CREATE PROCEDURE additems () BEGIN DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21'; DECLARE newids VARCHAR(128); SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE article_garmmature IN (grammatures); SELECT @newids; END; What i wan't is to store the id's as a comma seperated list into the newids VAR. I get the error that there is more than one result set. I also tried group_concat, but that didn't worked :( Anyone has an idea how to store multiple ids into one VAR ? Thanks for reading! Best wishes Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Navicat MySQL GUI for Linux version 8.0.23 is released.
Hmm. Speaking of Navicat, does anybody out there have an easy way to scrub the control coding from scripts developed under Navicat? I expect that they're there mostly for coloration on displays, but it's kind of obnoxious when you want to do anything else with them. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sun and mysql
Olaf Stein wrote: I am still amazed by the fact that youtube is worth 1.5 billion and MySQL AB barely 1 billion. Did they sell under price? Or does Google just have way to much many to spend/waste? Greetings from the just wondering... Olaf MySQL A.B., so far as I know, derives income from training, pubs sales, and enterprise support, with expense for salaries, space leases (at least some staff work from home), and equipment. The value of such an organization is inevitably based on somebody's best guess about future revenues. I'm encouraged about the prospects of MySQL when I remember that Sun is a major sponsor of open source application software. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql CPU 100%
Nik wrote: I've posted below the output of STATUS and SHOW GLOBAL STATUS. Any and all comments would be much appreciated as to how we can get performance back on track. Wow. . .200 logins? 511 open tables? 277+ million sort rows? On a single PC host? And you're complaining about performance? On the face of it, it sounds like you/'re letting the public beat hell out of that machine, and maybe it needs a little help. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for column value in a string variable?
OK, never mind. I finally found the 'locate' function. I knew it had to be there somewhere! -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search for column value in a string variable?
Hi. . .I'm trying to be lazy and write a query where I stuff a list of names into a variable (@namelist), and then try to find items in column lastname which appear anywhere in that list. It doesn't want to work, and even regexp isn't working, though that's probably my fault. Any suggestions? I'm completely open to various possibilities of delimiters in the namelist. Obviously this would let me reuse the query over and over, just changing the one line. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Browser - limit 1000 by default?
I'm assuming you're talking about the Mysql Query Browser? You can change the number of records under Tools- Preferences and changing the Max Rows For Generated Queries to whatever you want, set to zero removes the limit entirely. On Sat, 2007-12-01 at 15:17 -0600, Afan Pasalic wrote: Hi, on Linux version of MySQL Browser (v 1.2.4 beta), when double-click on any table, default query is SELECT * FROM table_name LIMIT 0,1000 On Win version (v 1.2.9 rc), there is no LIMIT part - what caused me to pull so many times tens, even hundreds thousands of records. I was looking for in setting for this feature, but wasn't able to find. is there way to set the limit value on default select query, on win version? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Browser - limit 1000 by default?
I'm talking about the Linux (ubuntu) version. On Sun, 2007-12-02 at 10:00 -0600, Afan Pasalic wrote: hi barry, this is on Linux version of MySQL Query Browser. I need the same on Win version. But, there is no such a solution (Tool Option ...). :( -afan barry wrote: I'm assuming you're talking about the Mysql Query Browser? You can change the number of records under Tools- Preferences and changing the Max Rows For Generated Queries to whatever you want, set to zero removes the limit entirely. On Sat, 2007-12-01 at 15:17 -0600, Afan Pasalic wrote: Hi, on Linux version of MySQL Browser (v 1.2.4 beta), when double-click on any table, default query is SELECT * FROM table_name LIMIT 0,1000 On Win version (v 1.2.9 rc), there is no LIMIT part - what caused me to pull so many times tens, even hundreds thousands of records. I was looking for in setting for this feature, but wasn't able to find. is there way to set the limit value on default select query, on win version? thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining number of vowels in a string
I'd rather do it in a sql statement rather than using a scripting language. I'm thinking you might be able to do one select, accumulating 5 siubstring counts (a,e,i,o,u) into 5 variables, and then sum the counts? I'll leave the testing to you. . .:-) Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
B. Keith Murphy wrote: Here is the kicker. Each box was a top of the line Sun server that had 32 processors and 32 gigs of RAM. They could handle up to 64 procs and 64 gigs. And each cost well over a million dollars for the hardware alone. Running Oracle on it must have cost over 100,000 dollars for software licenses. Granted this was in 2001, but the licensing cost for Oracle haven't gone down any that I am aware of...and the hardware cost will still be quite steep to do this type of thing. You youngsters may not realize that there were billing applications serving millions of customers long, long before there were any kind of database management systems. They employed concepts called flat files and batch processing. And they ran on machines far weaker than anything any of you have on your desk today. Even under something like MS Windows, it would be absolutely possible to configure 3-5 high speed printers and knock out 100,000 bills per hour from an Intel single CPU box. You really have no appreciation of how much power you actually have at your disposal. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Printing
I hope this isn't a silly question, or something covered in a FAQ. . .but is there any reason to not have at least some primitive print formatting commands in MySQL? Or am I missing something blindingly obvious? Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble connecting to server
At 11:36 AM 4/23/2007, Drew Burchett wrote: I have a mysql v 5.0 server running on Suse Linux 10.1. It has been running steadily and properly for several months now. However, when I came in this morning, my network card in the machine was bad. I replaced the card and reconfigured the network, but ever since then, I can only connect to mysql by using localhost. If I try to connect using the IP address, it simply hangs. If I do a netstat -aln | grep '3306', it shows mysql listening on all IP addresses, and it shows the connection to itself with the flag SYN_SENT. But that's as far as it goes. I've restarted the machine several times in vain hopes that it might kickstart something. I've also tried connecting from other machines, which seems to work perfectly. Any suggestions on how to proceed troubleshooting this? Reconfigured the network. . .Is it possible that the old IP address is no longer the one assigned to the machine? DHCP? Just a thought. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [LICENSING] why so hazy? Comparing to Samba.
At 05:00 PM 2/22/2007, mos wrote: On the other hand, if you developed a web application that ran on MySQL (an accounting package say) and you want to distribute it to 1000 MySQL users without giving them your source code, then you will need a MySQL AB license for each copy ($595,000 in total) even if you give the software away for free. The last line of the license notice reads: Contact MySQL AB if you need clarification of these terms or if you need to ask about alternative arrangements. This kind of suggests to me that they're willing to talk. I expect that as long as you're willing to pay *something*, there's probably a lot of room for negotiation. It's distinctly not in their interest to eliminate collateral development efforts. And there are already several products out there which do connect with MySQL and cost $100. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SET @var and insert that as cunting var into table with insert select
Hello Everyone! I am having a big problem with counting inserting rows. This is my Query: SELECT MAX(id) INTO @maxid FROM table1; // @maxid is now 44 INSERT INTO table2 (orderid, someothervars) SELECT @maxid +1, blahvar FROM table3; Okay my problem is, how do i increase the maxid? like that. table2: | id | orderid | someothervars ++-+--+ |1 |44 | blah | |2 |45 | blah | |3 |46 | blah | |4 |47 | blah | |5 |48 | blah | And so on. Anyone has an idea how to do something like that? I think it works somehow with SET @maxid = @maxid +1 But i fail combining it with INSERT SELECT :/ Any help is greatly appriciated =) My best wishes Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET @var and insert that as cunting var into table with insert select
Barry schrieb: Hello Everyone! i forgot i am using MySQL 4.11 Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET @var and insert that as cunting var into table with insertselect
Dušan Pavlica schrieb: Barry napsal(a): Hello Everyone! I am having a big problem with counting inserting rows. This is my Query: SELECT MAX(id) INTO @maxid FROM table1; // @maxid is now 44 INSERT INTO table2 (orderid, someothervars) SELECT @maxid +1, blahvar FROM table3; try select @maxid:=max(id)-1 from table1; insert into table2 (orderid, someothervars) SELECT @maxid:[EMAIL PROTECTED] +1, blahvar FROM table3; dusan Thanks dusan, thanks filip that works perfect :) Can you tell me where i can find that := function on the mysql dev site? Search function don't allow := and i didn't found it. That would be very nice :) My best regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpmysql don't answer
Thibaud Hulin schrieb: Hi! I'm a beginner with phpmysql, and after an installation, I can't access to my page http://localhost/phpmysql on Debian Etch. Is a problem of restarting mysql ? Thanks for help, Thibaud. Errors, warnings, logs? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to combine mysql structures?
Hi! I have several tables in my database. I have optimized the structures but i did that in an other database so that nothing went wrong. Okay how do i combine those structures now? Like making a dump of both structures and merge them somehow and then redump it back into the system and the tables have now the updated structure. Anyone know any tools or a way to do that? Many thanks for any help :) Regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any good free Case tools for MySQL 5.x?
mos wrote: Phil, FabForce doesn't work with MySQL 5 because of the new password encryption. Fabforce never lets me connect to a database. I suppose I could revert back to the old PW mgt scheme but that may weaken the security. There was an earlier post on this list which discussed new vs old password management. The gist is, that the configuration switch controls how *future* passwords are constructed. So, you can switch to old passwords, configure 1 userid for Fabforce with any privileges you choose, and switch to new passwords again. None of your existing arrangements are disturbed, but you can now use the single old-style userid to connect with DBD4, and keep your production users as secure as possible. I've just done this on Win XP, and it works very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
At 10:47 PM 11/23/2006, John Kopanas wrote: That is awesome... thanks. I still am not sure exactly though why this take 2 seconds while my methond took over a minute for the same amount of rows. In essence don't the two methods do the same things? No. Your approach was executing the subquery 2000 times for the 2000 records in your company file. And will run 500,000 times when you go to production data. Somebody with better math than I should try to project that. His prep query runs once, and his update query runs once. Scales very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some questions on Storage engine
At 10:20 PM 8/22/2006, Chris wrote: You can't store them in memory. http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html Despite what the doc says, I posted a working script here a couple of weeks ago which creates temporary tables with engine=Memory. Either my specification was being ignored in favor of some default--with no error indication, or somebody forgot to document a new feature. This has only been tried by me on Win XP. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql naming convention
Hello everyone! I am looking for a standard naming convention for databases. For example: is it good to use tablenames in column names like: table = tb_id,tb_text,tb_name and such. Probably there is some kind of overall naming convention out there, looked on google and such but only found conventions that people personally liked but no standards. Thanks for any help :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
Well, I said earlier that if I found a solution to this, I'd post it. Here it is, with many thanks to Nicholas Bernstein's timely July 7 post to the doc on user variables: It's not particularly elegant, it just gets the job done. If there is a cleaner way to do this, I'm not ashamed to be educated. Barry * Compquery.sql -- Compare Current Year Reg Numbers and Money to Prior Year */ /* */ /* */ /* ACCUMULATE DATA BY MONTH FOR BOTH YEARS*/ Drop Table If Exists Montable, Montable2; Create Temporary Table Montable engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From capclave2005reg where ( amount 0) Group by Monindex; Create Temporary Table Montable2 engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From Capclavepresent where ( amount 0) Group by Monindex; /* REPORT FOR BOTH YEARS WITH RUNNING TOTALS*/ Set @cumreg=0, @cumreg2=0, @cumpd=0, @cumpd2=0; Select Month, Year, Registrations, Paid RegIncom, Monindex, @cumreg:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd:= @cumpd+Paid RegIncomeYTD From Montable Union Select Month, Year, Registrations, Paid RegIncome, Monindex, @cumreg2:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd2:= @cumpd2+Paid RegIncomeYTD From Montable2 ; Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 04:15 PM 8/3/2006, Brent Baisley wrote: You might look into WITH ROLLUP. That could easily give you cumulative totals for the year, but off the top of my head I can't think of a way to get it for the months. - Original Message - From: Barry Newton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 10:29 PM Subject: Running Totals? Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: That's what happens with ROLLUP. I'm looking into a possible subquery approach just now. If it works, it will be worth it's own post. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running Totals?
Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 |23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 |11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 |48 | 200505 | | June | 2005 |45 | 200506 | | July | 2005 |10 | 200507 | | August| 2005 |17 | 200508 | | September | 2005 |58 | 200509 | | October | 2005 |97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg where year(DatePaid)=2005 and (amount 0 or Dealer = 'Y') Group by Monindex; Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 11:10 PM 8/2/2006, Peter Brawley wrote: Barry It would make life easier if I could also show a column with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex ; PB Looked promising, but gets me the following, which isn't quite right: +---+--+---+--+--+ | Month | Year | Registrations | Monindex | Year to date | +---+--+---+--+--+ | October | 2004 |23 | 200410 | 23 | | December | 2004 | 5 | 200412 |5 | | January | 2005 | 9 | 200501 | 14 | | February | 2005 |11 | 200502 | 16 | | April | 2005 | 2 | 200504 |7 | | May | 2005 |48 | 200505 | 53 | | June | 2005 |45 | 200506 | 50 | | July | 2005 |10 | 200507 | 15 | | August| 2005 |17 | 200508 | 22 | | September | 2005 |58 | 200509 | 63 | | October | 2005 |97 | 200510 | 102 | +---+--+---+--+--+ Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: swapping column values in update
At 06:35 PM 8/1/2006, Martin Jespersen wrote: I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this behavior in mysql makes perfect sense, so thats not the issue. If this is a one-time operation, it would seem easier to rename the columns. In some cases, even if it's a little more frequent than that. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql + LVS highjacked (mysql + NFS ramfs)
At 02:05 PM 7/25/2006, Winn Johnston wrote: after talking to a few people on the #mysql irc someone suggested using NFS to create a ramfs to get 100GB+ RAM shared memory to load the entire database into the RAM. Can anyone offer any Pros or Cons to this setup, drawing from personal expierence? thanks -winn johnston Do you really mean 100Gb RAM? Is that actually possible today? Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT and Asian languages
Peter Lauri schrieb: Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for sentence, but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri well you can use: WHERE text LIKE '%sentence%' is it that what you are looking for? You can also use regular expressions. These will also work on Asian text. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions Weirdness
Chris White schrieb: Normally I try to be as descriptive as possible with subject lines but.. not quite sure what else to call it. So basically, I was given ALL access by our sysadmin to a particular database. Now, somehow I was able to create a database and use it! It's my understanding that there is nothing grantable with all that would allow this (correct me if I'm wrong). Then to make it even better, whenever I access/run whatever on the database, permissions get totally whack and nothing can login anymore. I'd love to google this but am not quite sure where to even start. Thanks ahead of time for any ideas. mysql 5.0.22-standard There is a difference on User GRANTS and Table GRANTS. Mixed this up probably? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication from InnoDB to MyISAM
[EMAIL PROTECTED] schrieb: anyone know if there's any known issue with replication from InnoDB tables to MyISAM tables? I just switched a slave (mysql) to replicate from a different master that uses InnoDB and now I'm seeing weird problems on the slave. Table corruption, apps that can't connect etc. thanks, Jeff For me it reads like i want to replicate bananas but want to shape them like melons. The customers are confused that the melons taste like bananas Am i correct with this? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query on part of a date column?
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to log the execution time of each query
Saha, Mukul (Cognizant) schrieb: Hi, I would like to log the execution time for each query in my MySQL server .Is there any possible way? Please not that, the - log-slow-queries option will not suffice for my requirement. Thanks Regards Mukul Saha Hi, Well there is no option for logging every query. You can add a script to your service/software that does this. but as far as i know, there is no logging option for this. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: See lock table
Gabriel Mahiques schrieb: Friend, I need to see if a table is locked by some application or some user. Do you know some tools for this? (gpl license better). Or some sentence? When a table is locked, how can i unlock this table? My problem is that some applications cause an error and the user closes it with the task manager then the table remains locked. Regards InnoDB and edit the MyCnf with your preferred values. Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: space usage
Martin Jespersen schrieb: Does anyone havea clue of how mysql optimizes empty fields and how query speed is affected? Why don't you read the part in the mysql documentation about the opimization? what will be better for queryspeed/size: adding them with NULL using NULL as default or with NOT NULL using 0 and '' as defaults? Depends on what you need! But Both is okay. NULL woudl just give you more free space since NULL don't add any bytes to the column. Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: To those who responded - read the question. He wants to combine the values from the data column of *2* rows into one, not just a straightforward string concatenation. Sorry but you want me to write the whole SQL query? He has to use his brain. Grouping and joining the tables. I'm not here for doing your or his work! Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: Sorry but you want me to write the whole SQL query? He has to use his brain. Grouping and joining the tables. I'm not here for doing your or his work! Barry, I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. And therefore it's most important that he tries to learn how to look at the doc. Or your newbies will start asking every shit on List because the don't know what to do else. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. Well it was to be exact concating 2 tables with their rows. Well your post was also not sufficient, because you didn't helped him at all, too. So what do we learn about this? Nothing. It's internet! Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. And therefore it's most important that he tries to learn how to look at the doc. Remembering my own troubles learning MySQL, it can be difficult to know *what* to search for - if this person is completely new to SQL, the concept of joining a table to itself might not occur. That's why i gave a hint of where to look. Or your newbies will start asking every shit on List because the don't know what to do else. I know, it is annoying answering questions just because someone can't be bothered. I don't answer them. It's just annyoning to see the list overflowing with posts that had been easily done looking at the docs for a few minutes. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. Well it was to be exact concating 2 tables with their rows. Well your post was also not sufficient, because you didn't helped him at all, too. Yes, I know my post didn't answer the question either :) I mailed because I thought you might've misunderstood the original question and thought it was just about how to concatenate two strings, rather than the more difficult joining part. Well more difficult is relative. Did not misunderstood. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NDB API Guide
Stefan Hinz schrieb: Here's some information that's probably interesting for you if you're interested in MySQL Cluster ... As part of the effort of making MySQL Cluster a more mainstream product, it's been on our agenda for a very long time to rewrite the NDB API documentation so that it becomes usable by mere mortals. It took nearly three months to do this, because the new documentation is almost 300 pages long! The MySQL NDB API Guide covers two low-level APIs for writing applications that work with MySQL's NDB Cluster storage engine: - The NDB API is an object-oriented application programming interface for MySQL Cluster. The API provides ACID-compliant transactions, table and row scans that are similar in many ways to SQL cursors, and event handling. It also features object-oriented error-handling facilities. - The other API is the MySQL Cluster Management API (MGM API), a programming interface intended to provide administrative services for the cluster, such as starting and stopping Cluster nodes, Cluster logging, backups, and restoration from backups. All NDB API classes and subclasses are covered in the Guide, as is all of the MGM API function library. Also provided are a Cluster/NDB API glossary, class and relationshp diagrams, an index, and many examples. This new edition of the Guide covers the NDB API as implemented in MySQL 5.1. It's available for online viewing and download from here: - http://dev.mysql.com/doc/#ndbapi Regards, Stefan Good work :) Keep it up ! You guys are really doing great ;D With high regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
z247 schrieb:
Say I have the following tables;
siteID,name
--
site1, XYZ
site2, RSQ
ID,site,data
1, site1, M
2, site2, Q
3, site2, Y
4, site1, P
... etc.
And I want to create a view like this;
siteID,name,data
--
site1, XYZ, (M,P)
site2, RSQ, (Q,Y)
where all the related column data in the second table is placed in another
column. How can I do this? Is there a function that can group these values
into one variable or array?
Thank you
CONCAT_WS(', ',siteID,name,data)
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: array type
Nolan Rumble schrieb: mm.. I'm not sure what you're trying to achieve here? Why do you want an array in there? I'm pretty sure it's not possible (correct gang?), but I'm wondering why you'd want that. What you *can* do is just store a serialized array in a textfield. What i'm trying to do is dump a log file into a table. This log file contains various information like message ID, timestamp, etc. One of the fields in the logfile is a variable length -- recipients. So what i'd like to do is when I generate usage reports, for example, how many messages a user sent and received, then I can do something like the following: SELECT COUNT(*) FROM temp GROUP BY recipient; and it will list all the email addresses and how much email they sent/received. I suppose I can create another table which handles the variable length recipients but I would like to avoid that as that would make the SQL statements very complex and very hard to administer. Thanks NOlan why is it complex? It's a simple JOIN then. Isn't it? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem searching in grouped rows
Brent Baisley schrieb: I'll give it a shot. First, select the people that got the first advertisement: SELECT c_id,aa_id FROM adverticelink WHERE aa_id=4 From that result, you want to additionally filter out who didn't get the second advertisement. Since that information is contained in the same table, you want to do a self join. A self join will require you to use an alias name for the table, since you can't have two tables with the same name. We'll use a1 and a2 as the alias names. Additionally, you want to do a left join to retain all the records from your originally query. So you are actually joining the query of those who received the first ad, with those who received the second ad. Since you are doing a left join, those who didn't receive the second ad will not have a value for the aa_id field. It will be NULL. SELECT a1.c_id,a1.aa_id,a2.aa_id FROM adverticelink AS a1 LEFT JOIN adverticelink AS a2 ON (a1.c_id=a2.c_id AND a2.aa_id=6) WHERE a1.aa_id=4 AND a2.aa_id IS NULL That should work in 3.23. Yeah i know where you want to go to. This looks really good, never thought about rejoining the table. Thanks for that reply, it shed a light :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: your chance
Jad madi schrieb: Same here On Mon, 2006-06-26 at 06:46 -0300, João Cândido de Souza Neto wrote: I read that message and had no feelings. [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Please, read and let me know what do you feel I feel sad. There was no message ;_; -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem searching in grouped rows
Barry schrieb: Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry Noone? :( -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem searching in grouped rows
Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Helen M Hudson schrieb: Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen Well... MAX(date) AS latest_date_on_order, SUM(amount) FROM table GROUP BY date is it that what you looked for? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH and return some text
Taco Fleur schrieb: Hi all, is it possible to do a MATCH AGAINST and return some of the text, for example the first paragraph that contains the matching words? Kind regards, Well you can use the substring function if mysql for that. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html good luck Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mailing list protocals
Ligaya Turmelle schrieb: I have been subscribed to this list for a couple of years now. I don't often respond and rarely ask questions, but I do read it every day and typically learn something new. Around the 13th of this month I suddenly stopped receiving the mailing list (though I was able to send a question to the list). Are email addresses dropped from the list if they are inactive for a given period of time? Can notices be sent out before that to inform the readers? I ended up simply having to resubscribe to the mailing list to start receiving it again. And had to go into the archive to find my last question (which no one answered by the way :) ) Well yes. The list will kick you out after some time when you stop sending mails to it. I don't know how long it takes but the list do it. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user can see more than it's allowed to see?
Bing Du schrieb: Hello, I don't understand why user 'test1user' can see database 'test' as well. I think user 'test1user' should only be able to see database 'test1'. What did I do wrong here? I'd appreciate any help. Thanks. As root: mysql show databases; +---+ | Database | +---+ | LTM | | dummy | | mysql | | test | | test1 | +---+ 8 rows in set (0.00 sec) mysql grant all on test1.* to [EMAIL PROTECTED] identified by 'test1user'; Query OK, 0 rows affected (0.06 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED]| +--+ | GRANT USAGE ON *.* TO 'test1user'@'localhost' IDENTIFIED BY PASSWORD '3b8031664a43a963' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `test1`.* TO 'test1user'@'localhost' | +--+ 2 rows in set (0.00 sec) As user test1user: mysql show databases; +--+ | Database | +--+ | test | | test1| +--+ 2 rows in set (0.00 sec) Bing database test itself has Grants that it shows itself to everyone. You have to set this in the table database. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Docs team looking for tech writer
Jochem van Dieten schrieb: On 6/20/06, Stefan Hinz wrote: The MySQL documentation team is looking for another technical writer. For this we need the best and the most dedicated people around. You may work from anywhere in the world as long as you have the necessary skills and technical facilities to communicate across the Internet. Projects are coordinated from Germany, but our team members are expected to work independently. You should be prepared to work intensively with our developers when writing new documentation (in English, so you should be a native English speaker) Are you sure this is a requirement? To be able to do the job applicants might need to be fluent in English. Requiring that applicants have spoken English since early childhood (the definition of native English speaker) goes well beyond that: why can somebody who learned English later not be fluent in English? Jochem People don't know the right description of native speaker. Mostly here in germany they use it as for people who can do business conversations or stuff like this here. Technical conversations without running around with a dictionary. I wonder. Is this seasoning work or a full-time work? How much money do you get for that? Who don't ppl ever tell? I don't get it. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird error when creating a field
James Sherwood schrieb: Hello, Today we tried to create a new field on a table in one of our databases. When we tried to save the field we get the error: Error on rename of ./DBNAME/TABLENAME.MYI to ./DBNAME/#SQL2-210-174.MYI (Errorcode: 13) Has anyone ever seen this before? Thanks, James # perror 13 Error code 13: Permission denied Sure the own and group os mysql?? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The number of left join in one SQL statement.
Takanobu Kawabe schrieb: Hello, my name is Takanobu Kawabe. こんいちわ孝信さん：） I have some questions about left join SQL statement. I want to join some tables, and using left join statement . the system is the following. I use two machines. I created the same databases and tables on both machines. OS ： Debian Linux the newest stable 3.1 Server ： Apache 2.0 MySQL ： 5.0.20 PHP ： 5.1.4 OS ： WindowsXP Server ： Apache2.0 MySQL ： 5.0.19 PHP ： 5.1.2 And I considered the following SQL statement to select some columns from some tables using left join. SELECT `MST`.`no`, `MST`.`denhyono`, `GUS`.`TANI` AS `GUS`, `SUIDO`.`TANI` AS `SUIDO`, `DENKI`.`TANI` AS `DENKI`, `SYOKUIN1`.`SYOKUINNAME` AS `SYOKUIN1`, `SYOKUIN2`.`SYOKUINNAME` AS `SYOKUIN2`, `TANTOSYA`.`TANTOSYANAME` AS `TANTOSYA` FROM `MST` Left Join `TANIMST` AS `GUS` ON `MST`.`GUSno` = `GUS`.`no` Left Join `TANIMST` AS `SUIDO` ON `MST`.`SUIDOno` = `SUIDO`.`no` Left Join `TANIMST` AS `DENKI` ON `MST`.`DENKIno` = `DENKI`.`no` Left Join `SYOKUINMST` AS `SYOKUIN1` ON `MST`.`SYOKUINno1` = `SYOKUIN1`.`no` Left Join `SYOKUINMST` AS `SYOKUIN2` ON `MST`.`SYOKUINno2` = `SYOKUIN2`.`no` Left Join `SYOKUINMST` AS `TANTOSYA` ON `MST`.`TANTOSYAno` = `TANTOSYA`.`no`; - I tried this statement without error. But Ihave some questions. 1.How many left join keywords can I use in one SQL statement if there are 5000 datas in one table? As much as you like. there are no limitations. 2. when some customers use this query, can they read this query exactly? Depends on the MySQL knowledge of your customer. But when they are good in SQL they shouldn't have any problem with it. 3. Will the above query speed become slowly if there are 5000 datas in one table? Set indizes. This way your queries will speed up. Will the processing efficiency improve if I use multiple SQL statement? Always depends on the process. There are processes where multiple SQL queries come in handy. 4.Is there any other effective SQL statement I can get better processing efficiency? The beast way would be to start reading the optimization of MySQL: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Here you get a lot of helpful omtimizations for your queries. それじゃ。 Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Illegal mix of collations error
Dušan Pavlica schrieb:
Hello,
could someone help me to explain and resolve this error? Or maybe it is
a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT)
and (latin2_czech_cs,IMPLICIT) for operation '='
I don't know where latin2_general_ci collation comes from.
Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order field of a table
theo schrieb: Hi I' m a newbie and I'm sure this question has been answered many times, but I can't find the appropriate thread. Example: I have a table of people's data including a field called say importance like: NAME; AGE; FUNCTION; IMPORTANCE Peter; 24years, some job, 0 Anna; 22years, better job, 1 Frank; 40 years, chief, 2 Now, there is a new employee John with a job better than Peter's His importance is one more than peter's, and all other move up +1; So the list should look like this after inserting John: Peter; 24years, some job, 0 John; 28years, somewhat better job, 1 Anna; 22years, better job, 2 Frank; 40 years, chief, 3 Which is the most effiicient way (SQL Statements) to insert or delete John in this example? Thank you Well one solution would be updating the table first UPDATE workers SET impartance = importance + 1 WHERE importance 1 This would raise every importance by one where the importance is bigger than 1. Now you could insert john with the importance of 1. Is it that what you have been looking for? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL naming convention?
wolverine my schrieb: Hi! When writting a SQL scripts, do you follow any standard SQL naming convention? Similar to what described in http://kurafire.net/articles/sql-convention Yes. Most of the time. Depends always on where you work and what conventions were used. Does MySQL provides such naming convention guidelines and best practices? Nope. Since any name of a column has just to be syntactically correct. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated (fwd)
Gaspar Bakos schrieb:
-- Forwarded message --
Date: Mon, 12 Jun 2006 11:39:11 -0400 (EDT)
From: Gaspar Bakos
To: Barry [EMAIL PROTECTED]
Subject: Re: my-huge.cnf quite outdated
Hello, Barry,
RE:
Guess we would answer to everyone on the list who wishes to optimize his
cnf.
I don't guess, and don't even expect that you answer to everyone.
Well if everyone would start posting his cnf. You would end up
confused. That's what i pointed out with it.
Sometimes it is good to guess.
Oh, i have add super X RAMs with latencies of blah blah. Please i
think my cnf is outdated can somone help me? Or: Oh, i have added a
HD with 2times more rounds per/m can you update my cnf PLZ?
These are not what I asked, they are pretty negative exaggarations.
Well yes you did. Well they are negative but in the end you will face
this. There are no typical 8GB, SATA II 2TB filesystem RAID clustered,
with high connection network card configs out there that would run
smooothly on your system. It's just as it is.
You have to do it by yourself.
You would be lucky as hell finding somone with the same hardware config
as you have.
And yes. You can tweak the shit out of the mysql.cnf files.
You have to test yourself on your system.
This is what I am doing, and in the meantime, looking for experience,
and also sharing mine.
Experience is only gained by doing.
I did configured Server which had half a terrabyte of data and that was
no fun at all.
When you get further in this you will see that at the end you only have
two ways of accomplishing it.
Reading every manual you get about the cnf and get a hang on what every
variable do or get a customer contract and let somone configure it.
And btw. the cnf files wrk with even bigger tables than you have.
Not optimal but okay.
How big?
i tested them with 350 GB files on a 4GB RAM 500GB SATA raid1 system.
And was stable.
Every special server needs special handling. there is no the one and
only you have to do it this way way
OK, so why is there a my-{small,large,huge}.cnf ?
They are guidelines for typical systems and applications.
But they are quite outdated, as typical systems changed.
Because those cnf do give a push in a way you want to go but when you
want optimized configs you have to specialise it depending on the server.
That's what i said. nothing more or less
All in all: I was looking for _typical_ configs for 4GB+ machines and
100Gb+ tables.
Now guess this time. There isn't something like that.
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to set host table?
Takanobu Kawabe schrieb: Hello, my name is Takanobu Kawabe. I use MySQL 4.1.19 server now. I don't know how to set Host table in mysql database. For db table,tables_priv table and so on, can set using GRANT SQL statement. Can I use this statement for setting Host table? If I can, what SQL statement can I use? Otherwise, can I only insert the host table depended on db table? Hello Takanobu-san! This will surely help you: http://dev.mysql.com/doc/refman/4.1/ja/connection-access.html それじゃ ：） Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i decrypt password
Kaushal Shriyan schrieb: Hi I have a situation here how can i decrypt the mysql database userrs password Kaushal Not possible. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i decrypt password
Vitaliy Okulov schrieb: Здравствуйте, Paul. Вы писали 13 июня 2006 г., 17:13:52: At 14:09 +0200 6/13/06, Kim Christensen wrote: On 6/13/06, Barry [EMAIL PROTECTED] wrote: Kaushal Shriyan schrieb: Hi I have a situation here how can i decrypt the mysql database userrs password Kaushal Not possible. It is possible. However, that depends on which version of MySQL you are using - and which crypt method you are using for the passwords. mysql.user.Password is encrypted using the same algorithm as the PASSWORD() function. The algorithm changed in 4.1.0 and again in 4.1.1, but in all cases, it is one-way encryption. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Hm, but if I use bruteforce or dictionary method? It would take ages and you still wouldn't be sure if it works. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I cannot login MySQL Network
Takanobu Kawabe schrieb: Hello, my name is Takanobu Kawabe. I have gotten a MySQL Network, and the service level is gold , I could login MySQL Network on May 20th , but now I can't login. I have tried some times last week, and today , but I cannot do it. If I try to login (URL is https://network.mysql.com), the following message is displayed. Your email address [EMAIL PROTECTED] has multiple contracts with MySQL Network. You must choose only one contract to use for this login session. As I can continue to login there, when I press the button Continue Login, the following error message is displayed. Error: You need to be a MySQL Network customer in order to have access to this service. I want to know what is the reason of this error, and to solve this problem. Where can I callto solve this problem? Please tell me the telephone number. こんにちわ！ One thing might be that your email adress is not valid. Is it the exact error you get? = [EMAIL PROTECTED] is this the email you see on the error? If yes, then it could be because of your character encoding. Use UTF-8 encoding in your browser. Otherwise here is a telephone number: +1-208-514-4780 (14:00 - 01:00 UTC, Mon-Fri) Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
Gaspar Bakos schrieb: Hi, Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated? It says for systems with 512Mb RAM or more. Nowdays this is pretty basic setup, and 'huge' is probably something in excess of 4Gb RAM. I wonder if anyone has a recommendation for truly huge systems. For example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is fully devoted to serving the mysql daemon. The config I have (see below) has been tuned to be optimal for creating indexes on a large (100Gb+) single database table. It works fine (although not satisfactory), but I worry that some parameters may have an optimal value or range, and it does not make sense to increase them like crazy. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M # Cheers, # Gaspar Seriously. You should get a hang on the mysql.cnf vars and values. Guess we would answer to everyone on the list who wishes to optimize his cnf. Oh, i have add super X RAMs with latencies of blah blah. Please i think my cnf is outdated can somone help me? Or: Oh, i have added a HD with 2times more rounds per/m can you update my cnf PLZ? And yes. You can tweak the shit out of the mysql.cnf files. You have to test yourself on your system. My only opinion would be to test it on a server which has the same measures, so that the live project don't start acting weird. And btw. the cnf files wrk with even bigger tables than you have. Not optimal but okay. Every special server needs special handling. there is no the one and only you have to do it this way way Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied
Kaushal Shriyan schrieb: Hi ALL I am faced with a issue of creating database , The issue is I am able to enter to the MySQL Server with the password, but when i try to create database I get Access Denied Below are the details [EMAIL PROTECTED] root]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 to server version: 3.23.54 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql exit Bye [EMAIL PROTECTED] root]# mysqladmin -u root -p create kaushal Enter password: mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '[EMAIL PROTECTED]' to database 'kaushal'' Any Clue will be really appreciated Thanks Kaushal does the user root has create privileges? Is mysql installed as an other user than mysql? is the folder mysql also with mysql:mysql privileges? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL-on delete no action
Nenad Bosanac schrieb: Hi all I made mistake i n my last post so now i make it true. I have problem in MYSQL query tool. Well i put on foreign key on delete no action but next time when i edit that table it said that is on delete is restrict. Why is that ? I use MYSQL Query tool and my database is INNODB. Is there some kind of way to make it work or it is some kind of default value for innodb type of database? Thanks Sorry that english is very hard toi understand. Please post your queries probably that helps a bit more. Sorry no idea on what you want ^_^ -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett schrieb: To whom it may concern, I'm involved in lots of C# coding with several coders. I have a gripe with MySQL which may be easy to solve in future development. C# has two DateTime constants: DateTime.MinValue = '0001-01-01 00:00:00.000' DateTime.MaxValue = '-12-31 23:59:59.999' These are very useful to denote a date always beyond some data, or a date always before some data. Further, C# does not let you set DateTime to NULL. (very annoying!) These values therefore tend to creep into code where NULL would be better used. MySQL really doesn't like these values, it shows warnings: +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' | +-+--+-+ The real problem with these warning is: 1. This date is legal, if a little unlikely. 2. Any warning crash MySql.Data.dll!!! If there is a kindly developer watching these mailing lists, can I suggest MySQL accepts these values, at least for comparisons, a little cleaner? For what it's worth, and to help us C# coders... Ben When does this happen? What query had you run? A more insight might help fix the problem ;) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett schrieb: Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Well my msql doesn't give me any errors using that query. neither a warning. This might be a problem with windows. Windows has his problem using dates before 1st april 1970. Probably this is here going wrong. you can always compare ISO datetime formats to timestamps, Datetime fields or whatever. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett schrieb: Hi Barry, This is what I get: mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.25 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.00 sec) mysql SHOW WARNINGS; +-+--+-+ | Level | Code | Message| +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' for column 't' at row 1 | +-+--+-+ 1 row in set (0.00 sec) You can see MySQL complaining about the time format. Although I am using .NET, I am not using any windows code. This is MySQL on UNIX and .NET under Mono/Linux. As I said, this warning is annoying. It falls on a bug in the .NET MySQL library and causes it to crash nastily. Personally it annoys me because this warning is a bit of a misnomer. I am not assigning this value, only comparing to it... (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) But this is not a 'religious' thing. We can easily code around it. Until we hit a place we have forgotten this and crash Mono... So, I am only asking nicely to any kind MySQL developer on this list, please remove this warning :) Well removing 'explicit' warinings for every user having problems with 3rd party modules would have mysql without any warnings nowadays ;) i think that your mono should get more stable. i'm also using an older version of mysql this might be the readon why i don't get the arning. probably downgrade or well a path around might work also. I don't think any developer will remove that warning for that. ;) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duration query: how to ?
Bedford Bob schrieb: Hi, My clients can put their articles on my website. They pay for the real utilisation of the site. If a client has used 2 slots for all year, then he pays for them. Now my articles table has a starddate (when the client has started to sell his product) and an enddate (when the article is sold). I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days How to do so with a query ? fields are client.idclient, article.idclient, article.idarticle, article.startdate, article.enddate. Please help me, I don't know how to retrieve those values and I need them to get payed. Bob Use the DIF functions. What version of MySQL do you have? An other way would be to convert the Dates into UNIX STAMPS that way you could subtract them and you have then the seconds between each date. I don't think i have to tell you how to go on then. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with procedure
Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Mysql version is: 3.23.54 Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with prcedures
Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about log
Peng Yi-fan schrieb: Hi, I would like to change the path of a database's log. Which command I should choose? Thanks. Peng Change the config files ? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
RV Tec schrieb: Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). PS (*nix) should tell you how your processors are used. 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. Depends on which lib mysql uses in your case. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the best coding ethics related to mysql
abhishek jain schrieb: Hi all, I have been using mysql from last few years but for small projects only, recently i have been on to some good projects, I want toknow what is the best coding practices for mysql to kee it fast etc. I mean in mine earlier post one friend told me that size upto 4 GB can be achieved with Mysql. I want to know: 1)Which is better a long table in terms of nos. of columns or use join and increase the columns. eg. in simple registration site we have 20 columns , we should use it in same table or use it in two diff. tables. Depends on what you want to do with that table. If you have lotsa crossover questions and need to mix up stuff. use more tables. otherwise use less. 2)To use indexes to the maximum or restrain its use. To maximum. Read also the optimize sections of dev.mysql.com. They help a lot. 3)etc. Blah blah Pl. point me to good advanced tutorial of mysql. Also is there any certification of mysql, php etc. What kind of cert you are talking about? SSL certs or what? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching problem
Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Barry schrieb: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry Ok found a solution: WHERE concated_field REGEXP '(^|)87682(|$)' Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Wolfram Kraus schrieb: Barry wrote: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 WHERE concated_field LIKE '%87682%' It would also give me Fields that have 987682 or 876825. That's not what i looked for but thanks anyway ;) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
彭一凡 schrieb: try this: WHERE concated_field LIKE '87682%' or WHERE concated_field LIKE '87682next_' would give me 876825 what i am not looking for. And i were also looking for next87682. So this doesn't work. But thanks anyway :) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to solve this problem?
彭一凡 schrieb: Hi There is a schema example below: (From A first course in database system) Product (maker, model,type) Pc (model, speed, ram, hd, rd, price) Laptop (model, speed, ram, hd, screen, price) Printer (model, color, type, price) The statement below seems wrong based on MySQL 5.0, though it is from the Solutions: (SELECT maker, model, type AS productType FROM Product) RIGHT NATURAL OUTER JOIN ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer); Dose MySql support this statement? And how I can solve it? As far as i know you can only use JOINs with ON. So this would likely not work. If you tell what you want to be outputted we might help find a solution. barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Marcus Bointon schrieb: On 9 May 2006, at 14:27, Wolfram Kraus wrote: WHERE concated_field LIKE '%87682%' No, because that would also match numbers that contain that sequence like '187682next32876825'. 2. This doesn't sound like a good DB-Design, why don't you use two seperated fields for both numbers, or a m:n table if there are more possible entries? Definitely. Well not my one though :P But have to work with it ^^ -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: single database ... many aplications
jehova villa martinez schrieb: Hi, as newbie and with some trubles understanding English language, i have a question that I don’t know howto put on search engines (I don’t know technical keywords for my particular case). This is why I post here. This is the whole picture: I have four programs running on my place, Freeradius http://www.freeradius.org/ metadot web portal http://www.metadot.com/index_static.html mail toaster con qmail http://www.tnpi.biz/internet/mail/toaster/ y Xpanel http://www.xpanel.com/ each of them use MySQL and they has different databases and different tables...this four applications handle same usernames and password for same users, I mean same user has an e-mail account, and uses same username and password for authenticate freeradius modem access for a protmaster3, same username and password for login our metadot portal, and…. Same thing for access Xpanel virtual hosting for their home page. How can I use just one shared common database for use of all of my portal applications? Is this possible? It would be nice if I have a single login feature for all… is MySQL related topic or is particular hack for each program? Thank you for share any experience related or knowledge about this doubt. best regards from mexico, Yes. You have to set the GRANT provilegies that way, that Applications can connect to the Database which has the Users from outside. Just replace 'localhost' with '%' in the Mysql tables. That way the other servers can access this Database and get the Users from it. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
Nils Lastein schrieb: After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils Tried to load them into a hex-editor? Tried to find something to it in the docs? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATEDIFF and TIMEDIFF
Peter Lauri schrieb: Best groupmember, I run version 3.23.58 and need to use something similar to DATEDIFF and TIMEDIFF to calculate difference between two a timestamp and current_timestamp(). Is there any other function that is working for version 3.23.58 that do the same job? Best regards, Peter Lauri Cast it into seconds and calculate the difference. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table so slow to read
Gabriel Mahiques schrieb: My name is Gabriel, Saludos Cordiales is the same than Best Regard in spanish. The server explanin is the same. The table structure is the same, the application is the same (redirect the data source only), the quantity of record is the same. All is the same, I copy the database from one server to other But the servers are not the same, right? This could be one problem. The other problem might be missing indezies on your other server. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add a new value in an ENUM by manipulate .frm
Jörgen Winqvist schrieb:
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
Tried it?
I also use big tables but adding a field takes a few seconds.
Well it does take long if there is a key on the field.
That would rise the querytime.
So, i figure the values in enum:s are in the .frm file so why not change
it and leave the rest. I created a new empty table and altered it and
then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything here?
Probably the indizes might not work properly anymore.
But i am not so much into MySQL that i could tell you how MySQL works
behind ALTER TABLE.
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Select problem
I get this error: Fehler in /home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php in Zeile 36 mit Error:br Query:INSERT INTO objektflyer_verknuepfung (av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT 418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE av_id IN (1) brNot unique table/alias: 'objektflyer_verknuepfung' I use a very old Mysql. 3.2 or similiar. Anyone knows that error and can give a helping hand? Thanks, Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
