ORDER BY for ints
I'm getting a strange ordering when using ORDER BY on a int column. The rows are being returned sorted as follows: 1 10 11 12 13 14 15 2 3 4 5 6 7 8 9 I'm sure this is a simple one, but I haven't found an answer in the archives. Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with mysql.sock
Hello, I have recently installed FC3 and now I have PHP(4.4.9) and MYSQL(3.23) which got installed along with FC3. I'm able to connect to MYSQL from command prompt. But the problem fires when I do the same from a PHP script. The error I get is Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13). And I'm sure that MYSQL server is running and able tp connect from command prompt. I have been searching on the net for the same from the last 3 days but ended with nothing. I have changed php.ini to include the mysql_default_socket=/var/lib/mysql/mysql.sock. even then it doen't work. Earlier I had FC1 and everthing was working fine! Could please suggest me a solution to tackle this problem. Thanks in Advance Sandhya __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with mysql.sock
Hi Sandhya, Have you checked the permissions? The error message is System error: 13 = Permission denied. Is your PHP/Apache user able to connect via the socket? Try connecting as $ su php/apache user -c mysql -u user you connect with -p and see what happens. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Sandhya Reddy [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 September 2005 4:03 PM To: mysql@lists.mysql.com Subject: problem with mysql.sock Hello, I have recently installed FC3 and now I have PHP(4.4.9) and MYSQL(3.23) which got installed along with FC3. I'm able to connect to MYSQL from command prompt. But the problem fires when I do the same from a PHP script. The error I get is Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13). And I'm sure that MYSQL server is running and able tp connect from command prompt. I have been searching on the net for the same from the last 3 days but ended with nothing. I have changed php.ini to include the mysql_default_socket=/var/lib/mysql/mysql.sock. even then it doen't work. Earlier I had FC1 and everthing was working fine! Could please suggest me a solution to tackle this problem. Thanks in Advance Sandhya __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason snippety-snip Hi Jason If it's not too late (aren't timezones wonderful?) ;). Have you considered using an interim table into which you load your file in its entirety? Load all fields and have each field set to something like CHAR or VARCHAR big enough to accommodate the fields in the file. Then just pick the columns that you are interested in: Then you can do a : INSERT INTO final_table (col_1, col_2..col_n) SELECT col_1, IF(col_3=unknown, 0, col_3) AS col_2 FROM interim_table Also means that you don't have to necessarily upgrade to 5.x (as per your previous problem) - unless you want to, of course ;) Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documenting and visualizing a database
[quote] Linux The Linux release is not available right now but we are already working on the port. [/quote] raz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query when calling stored procedure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Fri, 23 Sep 2005, Jasper Bryant-Greene wrote: Thing is, even though I get that error, the server keeps running and accepting other connections and responding to queries fine. Probably because the mysqld_safe wrapper script has restarted it right after the crash. Take a look at the server's log files. My server log shows absolutely nothing after server startup, no matter how many times that stored procedure fails. I'm about to recompile with the debug USE flag (I'm on Gentoo) to try to track down the problem; once I've done that I'll file a bug. Before you file a bug, please re-test it with 5.0.13 - several SP-releated bugs have been fixed since 5.0.12 was released. If it's still repeatable in 5.0.13, please file a bug report at http://bugs.mysql.com. Thanks! Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany Are you MySQL certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFDOOvWSVDhKrJykfIRAh8uAJ4n1NLXykT3Ob65mTK/VT239cQTpACfZaW8 bym5rslnYYYrOeJTZOXvnjw= =0fJP -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections
Hi,
I guess it is a stupid simple question:
I have seen the following error in the log files:
DBI connect('database=[database]','[username]',...) failed: #08004Too many
connections at /[path_to_script] line 12
I have taken a look in my.cnf but I couldn't find some settings for
increasing the possible number of connections or something like that.
Can you tell me what can I do to do this?
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.13-rc has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 26 Sep 2005, Alex S Moore wrote: Has the md5sum been updated for the tarball (tar.gz) download? I tried two sites and got the same size file, i.e,. 19209618 bytes, but the md5sum does not agree. Yes, the MD5 checksum and the GPG signature were updated. This is how it looks on our local staging server: [EMAIL PROTECTED]:/build/stage/Downloads/MySQL-5.0 ls -l mysql-5.0.13-rc.tar.gz* - -rw-r--r--2 mysqldev users19209618 Sep 23 20:09 mysql-5.0.13-rc.tar.gz - -rw-r--r--2 mysqldev users 189 Sep 23 20:16 mysql-5.0.13-rc.tar.gz.asc - -rw-r--r--2 mysqldev users 57 Sep 23 20:16 mysql-5.0.13-rc.tar.gz.md5 [EMAIL PROTECTED]:/build/stage/Downloads/MySQL-5.0 md5sum -c mysql-5.0.13-rc.tar.gz.md5 mysql-5.0.13-rc.tar.gz: OK [EMAIL PROTECTED]:/build/stage/Downloads/MySQL-5.0 cat !$ cat mysql-5.0.13-rc.tar.gz.md5 d9500d81b4253142a2a3c68b53942aab mysql-5.0.13-rc.tar.gz [EMAIL PROTECTED]:/build/stage/Downloads/MySQL-5.0 gpg --verify mysql-5.0.13-rc.tar.gz.asc gpg: Signature made Fri 23 Sep 2005 08:16:31 PM MEST using DSA key ID 5072E1F5 gpg: Good signature from MySQL Package signing key (www.mysql.com) [EMAIL PROTECTED] Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany Are you MySQL certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFDOO0zSVDhKrJykfIRApP3AJ9bKOt/KCpfxXzktZ6WLAEETG4wCQCffWio bFhjCKxteWKkIKd0xXYzigI= =+nEv -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
Stephen A. Cochran Lists wrote: I'm getting a strange ordering when using ORDER BY on a int column. The rows are being returned sorted as follows: The list is typically the way to order a string. You are most likely to get meaningful suggestions to solve the mystery if you include the table definition (output of SHOW CREATE TABLE tablename) and the query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.13-rc has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 26 Sep 2005, Jim McAtee wrote: Is this a release candidate as the version in your message subject would imply, or is it an actual release, as your message states? Or does MySQL AB even bother to differentiate the two? I agree this is a bit confusing. As others pointed out, release candidate is just a different name for what we used to call gamma release before. Every version of MySQL that we publish is a release. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany Are you MySQL certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFDOO4OSVDhKrJykfIRAve7AJkBAYIEY6yDzsYUUTut9WHnBvX8BACfXwkL hiKdS+sJig3TRE1NeQrNyO4= =8FSH -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding index to a replication slave
Balazs Rauznitz wrote: I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Replication does nothing more or less than copying the queries that alter the tables (inserts, updates, alter table, delete, etc.) to the slave in the same order as they were executed on the master (it may be technically a different story, but this illustrates the concept). So, you can change the data or the database structure as much as you want, but errors may occur if the queries fail somehow. Adding indexes is okay as long as you don't add indexes that will cause duplicate key errors (e.g. a UNIQUE index on a field that will not contain unique values). Also, indexes may slow down the queries (more or less) because of the extra execution time needed to update the indexes. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote: You are most likely to get meaningful suggestions to solve the mystery if you include the table definition (output of SHOW CREATE TABLE tablename) and the query. mysql SHOW CREATE TABLE Player| + +--- --+ | Table | Create Table | + +--- --+ | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | + +--- --+ 1 row in set (0.00 sec) It now looks like mysql is returning the correct thing (at least on the command line), but for some reason inside php it's all screwedup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). The work can be automated easily with the right tools ;-) If you have for example perl installed on your system and the files all have the '.dat' extension, you can use: perl -pi -e 's/unknown/0/gi' *.dat All instances of 'unknown' (without the quotes of course and case insensitive) will be replaced with '0' in all of the .dat files; use different wildcard constructions if your file have other names. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
Stephen A. Cochran Lists wrote: On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote: You are most likely to get meaningful suggestions to solve the mystery if you include the table definition (output of SHOW CREATE TABLE tablename) and the query. mysql SHOW CREATE TABLE Player| | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | It now looks like mysql is returning the correct thing (at least on the command line), but for some reason inside php it's all screwedup What column are you ordering on? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding index to a replication slave
Mysql Lists wrote: I'm hoping they get replication setup by diffs, only sending the diffs that are tracked from the master.. replicated to the slave.. That would be sweet :) On 9/27/05, *Jigal van Hemert* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Balazs Rauznitz wrote: I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Replication does nothing more or less than copying the queries that alter the tables (inserts, updates, alter table, delete, etc.) to the slave in the same order as they were executed on the master (it may be technically a different story, but this illustrates the concept). Please reply to the list and not to me personally, so others can join the thread :-) Using diffs (I assume that you mean a set of records that were changed since the previous moment of synchronisation) is not the way MySQL replication works. The master 'simply' keeps a log of the modifying queries it performed and the slave reads that log from time to time. It's the simplest and safest way to replicate IMHO... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avg row length is varying a lot from oracle to MySQL
I am using the show table status command to find the average length of row in a table. And it reported something about 686 bytes. But as I populated more data, this number has substantially decreased to 484 bytes. But one more thing I also learnt is the average row length returned by oracle is just the avg length of each row in the data files ignoring the space occupied by the indexes. But I think MySQL is giving this values taking the space used by indexes into consideration. So they are almost coming the same. :) sujay -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 3:59 AM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: Re: Avg row length is varying a lot from oracle to MySQL Sujay Koduri wrote: we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. How are you measuring the size of a row in mysql? What makes you think it is averaging 686 bytes? Sujay Koduri also wrote: Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. Umm, using about 50 bytes out of 400 in variable length columns saves about 350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used _before_ indexes. On the other hand, you say it's only 180 in Oracle, so perhaps the estimate is off. OK, looking at your column definitions, I see 118 bytes worth of fixed-width columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes per row with empty varchars, 554 bytes per row with full varchars. With 40 to 50 chars used in the varchars, that would be around 180 bytes per row, just as in Oracle (not including any indexes). Of course, this is assuming you are using 1-byte chars. I can't imagine how that could take 686 bytes per row in mysql. It could just be a failure of my imagination, but you haven't yet shown us how you arrived at that number. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how can i get rid of this error ?
Hello I am a novice about mysql I use mysql4.0.24 with php4.3.10. When I upload jpeg/gif files to my webpage I get an error as below; PHP Warning: mysql_close(): supplied resource is not a valid MySQL-Link resource in /var/. What shall I do ? My php configuration as below; GD Support enabled GD Version 2.0 or higher GIF Read Support enabled GIF Create Support enabled JPG Support enabled PNG Support enabled WBMP Support enabled mysql MySQL Support enabled Active Persistent Links 0 Active Links 0 Client API version 4.0.24 MYSQL_MODULE_TYPE external MYSQL_SOCKET /tmp/mysql.sock MYSQL_INCLUDE -I/usr/local/include/mysql MYSQL_LIBS -L/usr/local/lib/mysql -lmysqlclient
Best way for maintaining a master table for use in several databases
Hello, I have a database scheme, containing a table, whichs content should be unique across all databases with this scheme (on several servers as well). It would be possible to define one database, in which all changes would be made. But what's the easiest way to keep the table consistent in the other databases? At the moment I can only think of reimporting the whole table in all databases any time a change is made or when requested, what seems pretty awkward. Perhaps triggers would be better, but I cannot switch to 5.0 (4.0 is used at the moment). Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar vs char speed improvement
2005/9/27, Ow Mun Heng [EMAIL PROTECTED]: Is there any doc looking at benchmarks of a database which is populated entirely with fixed length char compared to variable character lengths? I know using char is preferred over varchar when it comes to speed. Is there any available benchmarks available? I guess benchmarks depends on your data... varchar take really less space, and so is faster to read from disk, so you could improve speed in having varchar ! But since it cause dynamic row format, it can makes think slower. Pointers where would be appreciated. -- Ow Mun Heng Gentoo/Linux on DELL D600 1.4Ghz 1.5GB RAM 98% Microsoft(tm) Free!! Neuromancer 10:38:25 up 2 days, 15:24, 6 users, load average: 0.71, 0.56, 0.35 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advice Required
I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice Required
What are specifications of your DB. How much of data you have. How mant transactions you will be getting daily. Without these details it will be difficult to answer ur question. But for a centralised database server, I guess 1G RAM and 2CPU will be a good configuration to start with. sujay -Original Message- From: Vinayak Mahadevan [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 9:25 AM To: mysql@lists.mysql.com Subject: Advice Required I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. However, since MySQL is freely available, why not just download it, install it on your development machine, and run a few tests. The only real measurement of performance is actual tests: predictions often err, both high and low. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
[EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 Regards Vinayak -- Vinayak Mahadevan Systems Engineer Magtorq Pvt. Ltd. 58-C, Sipcot Industrial Complex Hosur - 635-126 Mobile: 98 94 90 61 61 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change table character set
Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/charset-upgrading.html http://dev.mysql.com/doc/mysql/en/charset-conversion.html MightyData wrote: I have a database that was created with MySQL 4.0. The character set for each table is latin1. I have upgraded the server to MySQL 4.1. I would like to change the character set for each table to utf8. What is the correct procedure? Can I just change the table character set with an alter statement or do I need to export data, alter table, then import data? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird database files
Hello. Yes, these files are from some unterminated query. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html You may want to use --start-position (--start-datetime) and --stop-position (--stop-datetime) to skip the problematic statement and perform necessary updates on the slave by hand. What versions of MySQL do you use? Jeff wrote: Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the binlog using mysqlbinlog on the master server. It complains that: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in log format or read error And then there are the weird table files that showed up in the data directory for the database (all MyISAM): -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI Anyone ever see something like this before? Are they files for a temp table maybe? Jeff -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedures and Functions
Hello. Have a look here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html However, it isn't clear for me what originally read data means. Do you store the time of the first access to the data in some table or somewhere else? You might obtain a better answer from the list members if your add more details about table structure and your application logic. I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Blue Wave Software wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Hello. You can parse the output of 'SHOW CREATE TABLE' or 'SHOW COLUMNS'. See: http://dev.mysql.com/doc/mysql/en/show-columns.html http://dev.mysql.com/doc/mysql/en/show-create-table.html Claire Lee wrote: I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the cpu utilization of mysqld
Hi, We are using the same set of query's what we re using in some other process, where the mysqld CPU utilization is minimum. But for this process particularly, mysqld is utilising more than 90 % of CPU. Even in 2GB RAM machine also, behaving same. We are using Windows XP/ 2000/ 2003 server. In all these it is behaving like this. Could any one of you suggest me the way to handle this situation. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 9:43 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Regarding the cpu utilization of mysqld [EMAIL PROTECTED] wrote on 09/26/2005 11:50:11 AM: Hi, When running a process which is a part of my apllication, mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for starting the mysql server. For other processes in the application, CPU utilization is very minimum. I used optimization (for order by, select and like) in building the sql queries used by the process and modified the following parameters in the my.ini file as key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 4M read_buffer_size = 4M query_cache_size= 16M I am using 256MB RAM. Are my above modifications are correct/useful for minimizing the CPU utilization?. Even after this also mysqld is using around 85% of CPU. Are there any other ways for minimizing the cpu utilization for mysql server. Could you please suggest me the ways for decreasing the CPU utilization for mysqld.exe to minimum. Please help me in this. Thanks, Narasimha Is it conceivable that the MySQL server could actually be that busy? How many SQL statements are you processing per second? How much data is transferring into and out of your MySQL server? What are some of the queries appearing in your slow query log? What do the EXPLAINs if those queries tell you? Besides MySQL, what else does that server host? What operating system is on that server? How much memory have you allocated for MySQL usage (some OS's allow for per-appication memory tuning)? Depending on what else is going on or how much memory all of your other applications/daemons have taken up, MySQL could be spending all of your CPU time just paging data. 256MB is not very much memory to install for a database server, especially a shared database server. May I suggest that you add more RAM (at least upgrade to 1GB RAM, more if you can afford it). Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 11:28:51: [EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 That sounds reasonably competent hardware. But it is not the number of connections that matters, it is the number and complexity of queries. One connection can generate a massive query which will lock out others; idle connections consume a small amount of memory but no other resources. I can only suggest you try to set up a representative test load and see if the performance is adequate for you. If performance is not adequate, after having examined your slow queries carefully and checked your indexing, extra ram is the first hardware upgrade to do. However, I think newcomers to MySQL are frequently surprised by its performance once properly indexed; don't spend money on extra ram until you have tried out a real (or simulated) test. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird database files
Jeff wrote: Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the binlog using mysqlbinlog on the master server. It complains that: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in log format or read error And then there are the weird table files that showed up in the data directory for the database (all MyISAM): -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI Anyone ever see something like this before? Are they files for a temp table maybe? Jeff Hello. Yes, these files are from some unterminated query. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html You may want to use --start-position (--start-datetime) and --stop-position (--stop-datetime) to skip the problematic statement and perform necessary updates on the slave by hand. What versions of MySQL do you use? On the master we're still running 4.0.16, the slaves are up to 4.1.13. To repair the problem with replication I simply restarted the master so it created another binlog and then took a snapshot and recreated the slaves. I found out just this morning however that one of the tables has a corrupted MYI file. When I try to run a query on it, I get... ERROR 1016: Can't open file: 'Mailbox_Old.MYI'. (errno: 144) Running perror I get: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird database files
Jeff wrote: Had problem with our database this weekend, apparently an app did an insert query that was huge size wise and this totally boogered up replication downstream. Also I cant read past that point in the binlog using mysqlbinlog on the master server. It complains that: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1953458240, event_type: 119 ERROR: Could not read entry at offset 66113944 : Error in log format or read error And then there are the weird table files that showed up in the data directory for the database (all MyISAM): -rw-rw1 mysqlmysql 14K Sep 12 11:50 #sql-7c1c_217c.frm -rw-rw1 mysqlmysql1.8G Sep 12 11:54 #sql-7c1c_217c.MYD -rw-rw1 mysqlmysql 92M Sep 12 12:09 #sql-7c1c_217c.MYI Anyone ever see something like this before? Are they files for a temp table maybe? Jeff Hello. Yes, these files are from some unterminated query. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html You may want to use --start-position (--start-datetime) and --stop-position (--stop-datetime) to skip the problematic statement and perform necessary updates on the slave by hand. What versions of MySQL do you use? On the master we're still running 4.0.16, the slaves are up to 4.1.13. To repair the problem with replication I simply restarted the master so it created another binlog and then took a snapshot and recreated the slaves. I found out just this morning however that one of the tables has a corrupted MYI file. When I try to run a query on it, I get... ERROR 1016: Can't open file: 'Mailbox_Old.MYI'. (errno: 144) Running perror I get: Error code 144: Unknown error 144 144 = Table is crashed and last repair failed I'm running mysqlcheck on the offending table now. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote: mysql SHOW CREATE TABLE Player| | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | What column are you ordering on? The command in PHP is: $query=SELECT id,first_name,last_name FROM Player ORDER BY id; $players=mysql_query($query); When issued from the mysql prompt, order is fine, but when called from php I'm getting that strange order: 1, 10, 11, 12, etc... Steve Cochran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson [EMAIL PROTECTED] wrote on 09/26/2005 10:58:02 PM: Many thanks for the earlier response to why LOAD DATA INFILE wasnt working for me. However, another problem has appeared. In the file I am reading, 2 of the fields are SUPPOSED to be float values. However, in several places, they are set to UNKNOWN. This seems to cause LOAD to abort. Is there a way for me to tell it to ignore this problem and just use the default value for the column? Jason One option is to stage that data into a table that has those columns defined as varchars. Then copy the data from there into the original destination table. This is also a way to get around your ignored columns problem of your previous post. Import everything into a flexible staging table (mostly varchars) that will accept the data. Then, only migrate from your staging table those columns you actually wanted in your data. Truncate or drop your staging table when you finish each batch. Sure it takes up more room but you can scrub your data in MySQL which may be easier for you to handle than trying to scrub the raw text files. I never take raw text data and merge it into a production database in one step. This is how I screen out bad inputs, malformed text, and otherwize invalid data. It sometimes takes 4 or 5 times to get the raw data into the staging table (depending on how messed up the raw data is). Once it's there, it's much easier for me to screen and fix. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Adding index to a replication slave
Jigal van Hemert [EMAIL PROTECTED] wrote on 09/27/2005 03:35:59 AM: Mysql Lists wrote: I'm hoping they get replication setup by diffs, only sending the diffs that are tracked from the master.. replicated to the slave.. That would be sweet :) On 9/27/05, *Jigal van Hemert* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Balazs Rauznitz wrote: I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Replication does nothing more or less than copying the queries that alter the tables (inserts, updates, alter table, delete, etc.) to the slave in the same order as they were executed on the master (it may be technically a different story, but this illustrates the concept). Please reply to the list and not to me personally, so others can join the thread :-) Using diffs (I assume that you mean a set of records that were changed since the previous moment of synchronisation) is not the way MySQL replication works. The master 'simply' keeps a log of the modifying queries it performed and the slave reads that log from time to time. It's the simplest and safest way to replicate IMHO... Regards, Jigal. Jigal is right, that's now how replication is currently works in MySQL. The current process is called Statement Based Replication (SBR). The developers are currently working the kinks out of Row Based Replication (RBR) which I believe is what Balazs was asking for. Look for it as a feature in 5.0 (or 5.1 I am not sure which tree I was lurking when I read the code changes). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how can i get rid of this error ?
Bulent [EMAIL PROTECTED] wrote on 09/27/2005 04:19:56 AM: Hello I am a novice about mysql I use mysql4.0.24 with php4.3.10. When I upload jpeg/gif files to my webpage I get an error as below; PHP Warning: mysql_close(): supplied resource is not a valid MySQL- Link resource in /var/. What shall I do ? My php configuration as below; GD Support enabled GD Version 2.0 or higher GIF Read Support enabled GIF Create Support enabled JPG Support enabled PNG Support enabled WBMP Support enabled mysql MySQL Support enabled Active Persistent Links 0 Active Links 0 Client API version 4.0.24 MYSQL_MODULE_TYPE external MYSQL_SOCKET /tmp/mysql.sock MYSQL_INCLUDE -I/usr/local/include/mysql MYSQL_LIBS -L/usr/local/lib/mysql -lmysqlclient Your upload code had a failure before it got to mysql_close() (probably due to trying to send a command larger than max_packet_length) so by the time it got to mysql_close() there was nothing left to close. Modify your PHP so that it detects and prints the errors for each database related statement and you will discover the real problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Best way for maintaining a master table for use in several databases
Neven Luetic [EMAIL PROTECTED] wrote on 09/27/2005 05:02:58 AM: Hello, I have a database scheme, containing a table, whichs content should be unique across all databases with this scheme (on several servers as well). It would be possible to define one database, in which all changes would be made. But what's the easiest way to keep the table consistent in the other databases? At the moment I can only think of reimporting the whole table in all databases any time a change is made or when requested, what seems pretty awkward. Perhaps triggers would be better, but I cannot switch to 5.0 (4.0 is used at the moment). Any ideas? Within a single server, you only need one copy of a table in one database. When you need data from it, just use the table's fully-qualified name and it won't matter which other database you are currently in (assuming that the account you are using has at least SELECT rights for the master table USE mysql; SELECT * From user; CREATE DATABASE testme; USE testme; SELECT * from mysql.user; Now to keep the table in sync between servers, use replication or the Federated storage engine. Remember that, outside of NDB, replication is only one-way! Even circular replication only happens in one direction around the circle and has some important limitations. The Federated storage engine is part of v5.0. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: problem with mysql.sock
I could be wrong but this may have something to do with ownership and permissions of the socket file. I recently upgraded my MySQL version and had basically the same problem. I can't remember though if I had to change the ownership to root.root or mysql.mysql. HTH, Ed On Mon, 26 Sep 2005, Sandhya Reddy wrote: Hello, I have recently installed FC3 and now I have PHP(4.4.9) and MYSQL(3.23) which got installed along with FC3. I'm able to connect to MYSQL from command prompt. But the problem fires when I do the same from a PHP script. The error I get is Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13). And I'm sure that MYSQL server is running and able tp connect from command prompt. I have been searching on the net for the same from the last 3 days but ended with nothing. I have changed php.ini to include the mysql_default_socket=/var/lib/mysql/mysql.sock. even then it doen't work. Earlier I had FC1 and everthing was working fine! Could please suggest me a solution to tackle this problem. Thanks in Advance Sandhya __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the cpu utilization of mysqld
[EMAIL PROTECTED] wrote on 09/27/2005 06:47:50 AM: Hi, We are using the same set of query's what we re using in some other process, where the mysqld CPU utilization is minimum. But for this process particularly, mysqld is utilising more than 90 % of CPU. Even in 2GB RAM machine also, behaving same. We are using Windows XP/ 2000/ 2003 server. In all these it is behaving like this. Could any one of you suggest me the way to handle this situation. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 9:43 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Regarding the cpu utilization of mysqld [EMAIL PROTECTED] wrote on 09/26/2005 11:50:11 AM: Hi, When running a process which is a part of my apllication, mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for starting the mysql server. For other processes in the application, CPU utilization is very minimum. I used optimization (for order by, select and like) in building the sql queries used by the process and modified the following parameters in the my.ini file as key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 4M read_buffer_size = 4M query_cache_size= 16M I am using 256MB RAM. Are my above modifications are correct/useful for minimizing the CPU utilization?. Even after this also mysqld is using around 85% of CPU. Are there any other ways for minimizing the cpu utilization for mysql server. Could you please suggest me the ways for decreasing the CPU utilization for mysqld.exe to minimum. Please help me in this. Thanks, Narasimha Is it conceivable that the MySQL server could actually be that busy? How many SQL statements are you processing per second? How much data is transferring into and out of your MySQL server? What are some of the queries appearing in your slow query log? What do the EXPLAINs if those queries tell you? Besides MySQL, what else does that server host? What operating system is on that server? How much memory have you allocated for MySQL usage (some OS's allow for per-appication memory tuning)? Depending on what else is going on or how much memory all of your other applications/daemons have taken up, MySQL could be spending all of your CPU time just paging data. 256MB is not very much memory to install for a database server, especially a shared database server. May I suggest that you add more RAM (at least upgrade to 1GB RAM, more if you can afford it). Shawn Green Database Administrator Unimin Corporation - Spruce Pine If your MySQL statement load on the problem server is the same as the load on another, similarly configured server and that the other server is not loading the CPU, you should probably look outside of MySQL. Check for anti-virus software, firewall packet screeners, disk change monitors, or any other programs running on the problem box that are either not running or are not configured the same way as they are on the well behaved server. Your last response said that MySQL can handle the load without maxing out the CPU, which tells me that MySQL is probably not the problem this time but that something that is reacting to MySQL and interfering with it probably is. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: ORDER BY for ints
Stephen A. Cochran Lists wrote: On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote: mysql SHOW CREATE TABLE Player| | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | What column are you ordering on? The command in PHP is: $query=SELECT id,first_name,last_name FROM Player ORDER BY id; $players=mysql_query($query); When issued from the mysql prompt, order is fine, but when called from php I'm getting that strange order: 1, 10, 11, 12, etc... Steve Cochran Then the problem is in your php code. Mysql will certainly return the rows ordered the same way to both the mysql client and to php. If php is showing a different order, then it must be something your php code is doing. If you post the code which displays the results, I'm sure someone could point out the problem, though that really belongs on a php list. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way for maintaining a master table for use in several databases
Neven Luetic [EMAIL PROTECTED] wrote on 09/27/2005 09:35:04 AM: Within a single server, you only need one copy of a table in one database. When you need data from it, just use the table's fully-qualified name and it won't matter which other database you are currently in (assuming that the account you are using has at least SELECT rights for the master table USE mysql; SELECT * From user; CREATE DATABASE testme; USE testme; SELECT * from mysql.user; If this is possible/feasible, depends on some issues concerning handling of authentication inside the underlying webapplication. OK, an example that's a little more concrete... Let's say that your master table is in the database CommonData. So I could run a query (from within CommonData) like this: SELECT * from masterdata WHERE color='blue'; or from any other database on the same server like this: SELECT * from CommonData.masterdata WHERE color='blue'; Now, let's say that application1 logs-in to the database server as user1, application2 logs-in as user2 but each uses a separate working database (app1 uses `carpentry` while app2 uses `painting`). In order to allow user1 into the `carpentry` database, you probably issued a GRANT similar to this: GRANT SELECT, INSERT, UPDATE, DELETE ON `carpentry`.* TO [EMAIL PROTECTED]; where xx.xx.xx.xx is the IP address of the server hosting application1. All you need to do is to grant SELECT permission to user1 on CommonData.masterdata like this: GRANT SELECT ON `CommonData`.`masterdata` TO [EMAIL PROTECTED]; That way each application will be able to login as itself and connect to their default database but still have access to your master data. Make sense? Now to keep the table in sync between servers, use replication or the Federated storage engine. Remember that, outside of NDB, replication is only one-way! Even circular replication only happens in one direction around the circle and has some important limitations. The Federated storage engine is part of v5.0. Replication is not an option, as the servers are already replicated for backup reasons, but partly onto other servers (not circular), so adding replication for this problem would create slaves with several masters. Cool. Thank You Neven Your're welcome :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. Always CC: the list on all responses. That way everyone can learn from the conversation.
Re: ORDER BY for ints
Michael Stassen wrote: Stephen A. Cochran Lists wrote: On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote: mysql SHOW CREATE TABLE Player| | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | What column are you ordering on? The command in PHP is: $query=SELECT id,first_name,last_name FROM Player ORDER BY id; $players=mysql_query($query); When issued from the mysql prompt, order is fine, but when called from php I'm getting that strange order: 1, 10, 11, 12, etc... Steve Cochran Then the problem is in your php code. Mysql will certainly return the rows ordered the same way to both the mysql client and to php. If php is showing a different order, then it must be something your php code is doing. If you post the code which displays the results, I'm sure someone could point out the problem, though that really belongs on a php list. Michael I had this same problem a while back, and while I'm probably making the same mistakes you are but have no idea what they are, I solved it by using ZEROFILL on the field I was sorting. So that PHP was seeing 0001, 0002, 0003... Worked for me, although from some of the replies I'm wondering if that wasn't the best way to do it. :/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 9:56 AM, Edward Vermillion wrote:
Michael Stassen wrote:
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name` varchar(32) NOT NULL default '',
`year` varchar(16) NOT NULL default '',
`height` varchar(8) NOT NULL default '',
`season` int(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `season` (`season`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
What column are you ordering on?
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
Thanks in advance.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
Hi,
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
Try with mysql_fetch_array
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
or it's likely that mysql_result retrieve an array of rows
(well-ordered), but fetch it by using a string for the index.
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0 Release Candidate
While I try to install MySQL 5.0 and start it as QCDA server, and upon execute it could not start service during MySQL Instance Configuration Wizard on Windows 2000 Pro. The older and removed Mysql are set disabled in services properties. Error no. 0 What is the workaroud here? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: ORDER BY for ints
On Sep 27, 2005, at 10:28 AM, Pooly wrote:
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
Try with mysql_fetch_array
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
or it's likely that mysql_result retrieve an array of rows
(well-ordered), but fetch it by using a string for the index.
This seems unlikely since the mysql_result takes a row number (int)
to select which fetched row to get a cell from (zero based).
Using mysql_fetch_array woulnd't work since I need to select a
certain row based on the order by, but not necessarily accessed in
sequence as shown in the code above.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connections
use a variable called max_connections( if its not there in my.cnf just add
it ) and restart mysql
eg . max_connections = 100
Kishore Jalleda
On 9/27/05, Octavian Rasnita [EMAIL PROTECTED] wrote:
Hi,
I guess it is a stupid simple question:
I have seen the following error in the log files:
DBI connect('database=[database]','[username]',...) failed: #08004Too many
connections at /[path_to_script] line 12
I have taken a look in my.cnf but I couldn't find some settings for
increasing the possible number of connections or something like that.
Can you tell me what can I do to do this?
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
linux timestamp
Hi all, I have a field in a mysql database v4.0.18 that contains a linux timestamp. I have been googleing for a solution that would return to me all the entries where the timestamp falls on, say a wednesday, or between 2pm to 3pm. I am led to believe that it is possible, but I have found no examples. Something like: SELECT * from listings WHERE timestamp(day==wednesday) or SELECT * from listings WHERE timestamp(14:00 = time = 15:00) Of course, I don't expect these examples to work, I'm just trying to illustrate what I'm trying to accomplish. Until now, I have been pulling all the fields and checking the timestamp with php. But I believe that there must be a better way. Thanks. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/109/carlisle_belinda.php Carlisle, Belinda Song Lyrics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To multi thread or NOT to multi thread?
Hi,
What makes me wonder is that the same test, with the code
stripped down, to my surprise, is significantly faster that the
multi threaded one, no matter how many times I run the tests. I am
including the code for both tests I run.
Since I couldn't find a good example of mutex locking the
following one is something that worked for me. However, I am not
sure if its as optimized as it should be, so I would appreciate an
expert's opinion about this. Is this a good example of mutex
locking? Are there any other better ways for this? Is this an OS
or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box.
Timings:
Thread Safe ON
0.14 real 0.01 user 0.10 sys
Thread Safe OFF
0.08 real 0.00 user 0.06 sys
Thnx,
Lefteris Tsinjelis
/*** MULTI THREADED EXAMPLE CODE ***/
/ -lmysqlclient_r -lpthread /
#include stdarg.h
#include stdio.h
#include stdlib.h
#include string.h
#include pthread.h
#include mysql.h
#define MAX 100
typedef struct db_donfig {
char host[16];
char user[16];
char pass[16];
char name[16];
unsigned int port;
char *socket;
} db_config;
typedef struct db_mutex {
MYSQL *db;
pthread_mutex_t lock;
} db_mutex;
db_mutex dbm;
void*db_pthread(void *arg);
static void db_die(MYSQL *db, char *fmt, ...);
MYSQL*db_connect(MYSQL *db, db_config *dbc);
void db_disconnect(MYSQL *db);
long db_query(MYSQL *db, const char *query);
int main(int argc, char **argv) {
int i;
pthread_t pthread[MAX];
db_config dbc;
strcpy(dbc.host,localhost);
strcpy(dbc.user,root);
strcpy(dbc.pass,);
strcpy(dbc.name,);
dbc.port = 3306;
dbc.socket = NULL;
dbm.db = db_connect(dbm.db, dbc);
pthread_mutex_init(dbm.lock, pthread_mutexattr_default);
if (!mysql_thread_safe())
fprintf(stderr, Thread Safe OFF\n);
else
fprintf(stderr, Thread Safe ON\n);
pthread_setconcurrency(4);
// fire up the threads
for (i = 0; i MAX; ++i)
pthread_create(pthread[i], NULL, db_pthread, NULL);
// wait for threads to finish
for (i = 0; i MAX; ++i)
pthread_join(pthread[i], 0);
pthread_mutex_destroy(dbm.lock);
db_disconnect(dbm.db);
exit(EXIT_SUCCESS);
}
void *db_pthread(void *arg) {
db_query(dbm.db, show status);
pthread_exit((void *)0);
}
static void db_die(MYSQL *db, char *fmt, ...) {
va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
db_disconnect(db);
exit(EXIT_FAILURE);
}
MYSQL *db_connect(MYSQL *db, db_config *dbc) {
if ( !(db = mysql_init(db)) )
db_die(db, mysql_init failed: %s, mysql_error(db));
else {
if ( !mysql_real_connect(db, dbc-host, dbc-user, dbc-pass, dbc-name,
dbc-port, dbc-socket, 0) )
db_die(db, mysql_real_connect failed: %s, mysql_error(db));
}
return (db);
}
void db_disconnect(MYSQL *db) {
if (db)
mysql_close(db);
}
long db_query(MYSQL *db, const char *query) {
long ret;
pthread_mutex_lock(dbm.lock);
ret = mysql_query(db, query);
// if query failed, exit with db error
if (ret != 0) {
pthread_mutex_unlock(dbm.lock);
db_die(db, mysql_query failed: %s, mysql_error(db));
}
// if query succeeded
else {
MYSQL_RES *res;
res = mysql_store_result(db);
pthread_mutex_unlock(dbm.lock);
// if there are rows
if (res) {
MYSQL_ROW row, end_row;
unsigned int num_fields;
num_fields = mysql_num_fields(res);
while ( (row = mysql_fetch_row(res)) )
for (end_row = row + num_fields; row end_row; ++row)
++ret;
mysql_free_result(res);
}
// if there are no rows, should there be any ?
else {
// if query was not a SELECT, return with affected rows
if(mysql_field_count(db) == 0)
ret = mysql_affected_rows(db);
// there should be data, exit with db error
else
db_die(db, mysql_store_result failed: %s, mysql_error(db));
}
}
return (ret);
}
/** NO MULTI THREADED EXAMPLE CODE **/
/** -lmysqlclient ***/
#include stdarg.h
#include stdio.h
#include stdlib.h
#include string.h
#include pthread.h
#include mysql.h
#define MAX 100
typedef struct db_donfig {
char host[16];
char user[16];
char pass[16];
char name[16];
unsigned int port;
char *socket;
} db_config;
typedef struct db_mutex {
MYSQL *db;
pthread_mutex_t lock;
} db_mutex;
db_mutex dbm;
static void db_die(MYSQL *db, char *fmt, ...);
MYSQL*db_connect(MYSQL *db, db_config *dbc);
void db_disconnect(MYSQL *db);
long db_query(MYSQL *db, const char
Re: linux timestamp
On 27/09/2005, Dotan Cohen wrote: Hi all, I have a field in a mysql database v4.0.18 that contains a linux timestamp. I have been googleing for a solution that would return to me all the entries where the timestamp falls on, say a wednesday, or between 2pm to 3pm. SET @uts := UNIX_TIMESTAMP(NOW()); SELECT IF(WEEKDAY(FROM_UNIXTIME(@uts)) = 2, 'Yes', 'No') AS 'Wednesday?'; SELECT IF(TIME(FROM_UNIXTIME(@uts)) BETWEEN '14:00' AND '15:00', 'Yes', 'No') AS 'Between 2 and 3 pm?'; See http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linux timestamp
Dotan Cohen wrote: Hi all, I have a field in a mysql database v4.0.18 that contains a linux timestamp. I have been googleing for a solution that would return to me all the entries where the timestamp falls on, say a wednesday, or between 2pm to 3pm. I am led to believe that it is possible, but I have found no examples. Something like: SELECT * from listings WHERE timestamp(day==wednesday) or SELECT * from listings WHERE timestamp(14:00 = time = 15:00) If you're wanting to do queries like that regularly, you should set up columns containing the weekday and the hour and index them. For a one-shot, you can use a query something like SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%W')) = 'Wednesday'; or SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%H')) = '14'; but it won't be fast if the table is big. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linux timestamp
Dotan Cohen wrote:
Hi all, I have a field in a mysql database v4.0.18 that contains a
linux timestamp. I have been googleing for a solution that would
return to me all the entries where the timestamp falls on, say a
wednesday, or between 2pm to 3pm. I am led to believe that it is
possible, but I have found no examples. Something like:
SELECT * from listings WHERE timestamp(day==wednesday)
or
SELECT * from listings WHERE timestamp(14:00 = time = 15:00)
Fair warning: Because MySQL won't be able to make proper use of it's
indexes the following queries will be VERY slow with any reasonable
sized data set. If your going to be performing these queries often I'd
recommend either storing the field as a datatime (you can do date time
to unixtime conversion in MySQL using the unix_timestamp() function) or
denormalising the data and storing both.
SELECT * from listings WHERE date_format('%W',from_unixstamp(timestamp)) =
'Wednesday';
SELECT * from listings WHERE cast( date_format('%H',from_unixstamp(timestamp))
as unsigned) between 14 and 15;
Of course, I don't expect these examples to work, I'm just trying to
illustrate what I'm trying to accomplish. Until now, I have been
pulling all the fields and checking the timestamp with php. But I
believe that there must be a better way. Thanks.
Dotan Cohen
http://lyricslist.com/lyrics/artist_albums/109/carlisle_belinda.php
Carlisle, Belinda Song Lyrics
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To multi thread or NOT to multi thread?
Hi,
I think I can shed a bit of light on the topic. There are several reasons
why your multithreaded code is not a good example and would be slower.
1) locking/unlocking mutexes of course does add *some* overhead
2) you have a single database connection and are passing it around between
threads thus serializing the actual queries, as such the queries are not
multithreaded at all and your code is kind of a silly use of threading --
this combined with #1 above naturally does make your threaded code slower
3) show status may not be a good example of threaded performance server
side -- A better test would be a variety of different insert queries or
such, or changes to different tables. Depending on your table type some
locking may occur on inserts that can serialize them if you are inserting
the same data or data on the same data page in the database, more disparate
queries however will actually execute in parallel and should see a speed
increase.
John
On 9/27/05, Lefteris Tsintjelis [EMAIL PROTECTED] wrote:
Hi,
What makes me wonder is that the same test, with the code
stripped down, to my surprise, is significantly faster that the
multi threaded one, no matter how many times I run the tests. I am
including the code for both tests I run.
Since I couldn't find a good example of mutex locking the
following one is something that worked for me. However, I am not
sure if its as optimized as it should be, so I would appreciate an
expert's opinion about this. Is this a good example of mutex
locking? Are there any other better ways for this? Is this an OS
or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box.
Timings:
Thread Safe ON
0.14 real 0.01 user 0.10 sys
Thread Safe OFF
0.08 real 0.00 user 0.06 sys
Thnx,
Lefteris Tsinjelis
/*** MULTI THREADED EXAMPLE CODE ***/
/ -lmysqlclient_r -lpthread /
#include stdarg.h
#include stdio.h
#include stdlib.h
#include string.h
#include pthread.h
#include mysql.h
#define MAX 100
typedef struct db_donfig {
char host[16];
char user[16];
char pass[16];
char name[16];
unsigned int port;
char *socket;
} db_config;
typedef struct db_mutex {
MYSQL *db;
pthread_mutex_t lock;
} db_mutex;
db_mutex dbm;
void *db_pthread(void *arg);
static void db_die(MYSQL *db, char *fmt, ...);
MYSQL *db_connect(MYSQL *db, db_config *dbc);
void db_disconnect(MYSQL *db);
long db_query(MYSQL *db, const char *query);
int main(int argc, char **argv) {
int i;
pthread_t pthread[MAX];
db_config dbc;
strcpy(dbc.host,localhost);
strcpy(dbc.user,root);
strcpy(dbc.pass,);
strcpy(dbc.name http://dbc.name,);
dbc.port = 3306;
dbc.socket = NULL;
dbm.db = db_connect(dbm.db, dbc);
pthread_mutex_init(dbm.lock, pthread_mutexattr_default);
if (!mysql_thread_safe())
fprintf(stderr, Thread Safe OFF\n);
else
fprintf(stderr, Thread Safe ON\n);
pthread_setconcurrency(4);
// fire up the threads
for (i = 0; i MAX; ++i)
pthread_create(pthread[i], NULL, db_pthread, NULL);
// wait for threads to finish
for (i = 0; i MAX; ++i)
pthread_join(pthread[i], 0);
pthread_mutex_destroy(dbm.lock);
db_disconnect(dbm.db);
exit(EXIT_SUCCESS);
}
void *db_pthread(void *arg) {
db_query(dbm.db, show status);
pthread_exit((void *)0);
}
static void db_die(MYSQL *db, char *fmt, ...) {
va_list ap;
va_start(ap, fmt);
vfprintf(stderr, fmt, ap);
va_end(ap);
(void)putc('\n', stderr);
db_disconnect(db);
exit(EXIT_FAILURE);
}
MYSQL *db_connect(MYSQL *db, db_config *dbc) {
if ( !(db = mysql_init(db)) )
db_die(db, mysql_init failed: %s, mysql_error(db));
else {
if ( !mysql_real_connect(db, dbc-host, dbc-user, dbc-pass, dbc-name,
dbc-port, dbc-socket, 0) )
db_die(db, mysql_real_connect failed: %s, mysql_error(db));
}
return (db);
}
void db_disconnect(MYSQL *db) {
if (db)
mysql_close(db);
}
long db_query(MYSQL *db, const char *query) {
long ret;
pthread_mutex_lock(dbm.lock);
ret = mysql_query(db, query);
// if query failed, exit with db error
if (ret != 0) {
pthread_mutex_unlock(dbm.lock);
db_die(db, mysql_query failed: %s, mysql_error(db));
}
// if query succeeded
else {
MYSQL_RES *res;
res = mysql_store_result(db);
pthread_mutex_unlock(dbm.lock);
// if there are rows
if (res) {
MYSQL_ROW row, end_row;
unsigned int num_fields;
num_fields = mysql_num_fields(res);
while ( (row = mysql_fetch_row(res)) )
for (end_row = row + num_fields; row end_row; ++row)
++ret;
mysql_free_result(res);
}
// if there are no rows, should there be any ?
else {
// if query was not a SELECT, return with affected rows
if(mysql_field_count(db) == 0)
ret = mysql_affected_rows(db);
// there should be data, exit with db error
else
db_die(db, mysql_store_result failed: %s, mysql_error(db));
}
}
return (ret);
}
/** NO MULTI THREADED EXAMPLE CODE **/
/** -lmysqlclient ***/
#include stdarg.h
#include
Re: To multi thread or NOT to multi thread?
John McCaskey wrote: Hi, I think I can shed a bit of light on the topic. There are several reasons why your multithreaded code is not a good example and would be slower. 1) locking/unlocking mutexes of course does add *some* overhead *lots* would probably be a better choice here! :) 2) you have a single database connection and are passing it around between threads thus serializing the actual queries, as such the queries are not multithreaded at all and your code is kind of a silly use of threading -- this combined with #1 above naturally does make your threaded code slower But this is what I had in mind though. I wanted to be that way instead of opening multi threaded connections but, from the looks of it, I guess you are right and its not really worth the trouble. I have read somewhere that opening a few connections can be slower but I guess that was probably wrong. 3) show status may not be a good example of threaded performance server side -- A better test would be a variety of different insert queries or such, or changes to different tables. Depending on your table type some locking may occur on inserts that can serialize them if you are inserting the same data or data on the same data page in the database, more disparate queries however will actually execute in parallel and should see a speed increase. I have tried with other queries, some random ones as well, and the results where very similar. Thnx, Lefteris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To multi thread or NOT to multi thread?
Hi again, On 9/27/05, Lefteris Tsintjelis [EMAIL PROTECTED] wrote: John McCaskey wrote: Hi, I think I can shed a bit of light on the topic. There are several reasons why your multithreaded code is not a good example and would be slower. 1) locking/unlocking mutexes of course does add *some* overhead *lots* would probably be a better choice here! :) Well, it certainly depends on how much lock contention there is. In a well designed multi-threaded app you want to minimize the lock contention so that it will be minimal. In your example lock contention is very heavy, so in that case *lots* may be a better word! 2) you have a single database connection and are passing it around between threads thus serializing the actual queries, as such the queries are not multithreaded at all and your code is kind of a silly use of threading -- this combined with #1 above naturally does make your threaded code slower But this is what I had in mind though. I wanted to be that way instead of opening multi threaded connections but, from the looks of it, I guess you are right and its not really worth the trouble. I have read somewhere that opening a few connections can be slower but I guess that was probably wrong. Ok, I can understand why you would have it in mind, but it's going to be a bad idea. Opening multiple connections will of course add some overhead, but its a different kind. What you have to ask yourself is whether the queries you are running in seperate threads are capable of being run in parallel server side. If so then opening multiple threads will be a performance win. If the queries you are running will get serialized on the server anyway then the extra overhead of the additional connections will slow you down. The current implementation you have however will always be slower than doing it without threading as thats esentially what happens with your lock contention anyway. 3) show status may not be a good example of threaded performance server side -- A better test would be a variety of different insert queries or such, or changes to different tables. Depending on your table type some locking may occur on inserts that can serialize them if you are inserting the same data or data on the same data page in the database, more disparate queries however will actually execute in parallel and should see a speed increase. I have tried with other queries, some random ones as well, and the results where very similar. Yep, your example would have the same results with any query due to the above mentioned serialization and lock contention you have. However, if you fixed that and opened one connection per thread (or a pool of say 5 connections that would be shared by 5-n threads) then the type of query and how it executes server side is going to have a definate impact. Good luck! Thnx, Lefteris
MySQL 5.0 Release Candidate
MySQL 5.0 was installed up to the point where I tried to start it in Instnace Configuration wizard. It won't start at all even mysqld and mysqld --safe-mode I'm getting frustrated -- hosed hard drive, reinstalled Windows 2000 Pro and installed MySQL 5.0 and it still *wont* run. -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: MySQL 5.0 Release Candidate
Scott Hamm [EMAIL PROTECTED] wrote on 09/27/2005 11:25:15 AM: While I try to install MySQL 5.0 and start it as QCDA server, and upon execute it could not start service during MySQL Instance Configuration Wizard on Windows 2000 Pro. The older and removed Mysql are set disabled in services properties. Error no. 0 What is the workaroud here? It sounds as though you haven't completely uninstalled your existing MySQL server(s). Are you trying to do paralell installs? What method of installation are you using? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: To multi thread or NOT to multi thread?
2005/9/27, Lefteris Tsintjelis [EMAIL PROTECTED]: Hi, What makes me wonder is that the same test, with the code stripped down, to my surprise, is significantly faster that the multi threaded one, no matter how many times I run the tests. I am including the code for both tests I run. Since I couldn't find a good example of mutex locking the following one is something that worked for me. However, I am not sure if its as optimized as it should be, so I would appreciate an expert's opinion about this. Is this a good example of mutex locking? Are there any other better ways for this? Is this an OS or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box. You ran several queries with multiple thread, fine, but they are all serialised over one connection, so you get all the overhead of locking and thread-creation, for no advantage... So that's the result expected. (So, yes forthe troll, it's an OS issue, threads creation are somewhat slow on FreeBSD :) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 Release Candidate
Scott Hamm [EMAIL PROTECTED] wrote on 09/27/2005 03:00:00 PM: MySQL 5.0 was installed up to the point where I tried to start it in Instnace Configuration wizard. It won't start at all even mysqld and mysqld --safe-mode I'm getting frustrated -- hosed hard drive, reinstalled Windows 2000 Pro and installed MySQL 5.0 and it still *wont* run. I wish I had seen this 15 seconds ago (I just responded to your earlier post). What do you mean hosed hard drive ? How are you attempting to install? This was a Win2K3 server earlier today, wasn't it? Are you following the documented installation procedure or are you trying some other method(s)? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: MySQL 5.0 Release Candidate
I had the same problem. Disabling it isn't enough. I had to use the sc command from the Windows command prompt to delete the service. Just enter sc on the command line and a description of the command will be displayed. Fred -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 10:25 AM To: 'Mysql ' Subject: MySQL 5.0 Release Candidate While I try to install MySQL 5.0 and start it as QCDA server, and upon execute it could not start service during MySQL Instance Configuration Wizard on Windows 2000 Pro. The older and removed Mysql are set disabled in services properties. Error no. 0 What is the workaroud here? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange order by problem
Try this
mysql select distinct secname, date
- from optresult
- where secname like 'swap%'
-and date like '2005-09-2%'
- order by if(secname like 'swap%',
- (mid(secname,5,20)+0),
- secname);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-21 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
+--++
18 rows in set (0.00 sec)
-Original Message-
From: Claire Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
Claire Lee [EMAIL PROTECTED] wrote on 09/27/2005 03:48:11 PM:
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
So you want to sort by secname except when secname starts with 'SWAP'
ORDER BY secname
, if (secname like 'swap%'
,(right(secname, length(secname)-locate('p', secname))+0)
,0)
, date;
by giving every *other* entry a default second sort-by of 0, they end up
all sorting according to secname then date. It's when secname starts with
swap that you get the sub-sorting value according to the end of the
string. Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: strange order by problem
Claire Lee wrote: I need to order a few names by the number following the main name. For example swap2, swap3, swap10 in the order of swap2, swap3, swap10, not in swap10, swap2, swap3 as it will happen when I do an order by. ... ORDER BY ABS(SUBSTRING(secname,5)) ... will insure that the trailing digits are treated as numbers :-) +--++ | secname | date | +--++ | SWAP0.25 | 2005-09-21 | | SWAP0.5 | 2005-09-21 | | SWAP1| 2005-09-21 | | SWAP10 | 2005-09-26 | | SWAP10 | 2005-09-23 | | SWAP10 | 2005-09-21 | | SWAP2| 2005-09-26 | | SWAP2| 2005-09-23 | | SWAP2| 2005-09-22 | | SWAP2| 2005-09-21 | | SWAP3| 2005-09-21 | | SWAP3| 2005-09-26 | | SWAP3| 2005-09-23 | | SWAP3| 2005-09-22 | | SWAP5| 2005-09-21 | | SWAP5| 2005-09-26 | | SWAP5| 2005-09-23 | | SWAP5| 2005-09-22 | +--++ HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To multi thread or NOT to multi thread?
Pooly wrote: 2005/9/27, Lefteris Tsintjelis [EMAIL PROTECTED]: Hi, What makes me wonder is that the same test, with the code stripped down, to my surprise, is significantly faster that the multi threaded one, no matter how many times I run the tests. I am including the code for both tests I run. Since I couldn't find a good example of mutex locking the following one is something that worked for me. However, I am not sure if its as optimized as it should be, so I would appreciate an expert's opinion about this. Is this a good example of mutex locking? Are there any other better ways for this? Is this an OS or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box. You ran several queries with multiple thread, fine, but they are all serialised over one connection, so you get all the overhead of locking and thread-creation, for no advantage... So that's the result expected. (So, yes forthe troll, it's an OS issue, threads creation are somewhat slow on FreeBSD :) My intention was to avoid the overhead of multiple network connections and I didn't expect it to have that much difference. It is probably an OS issue a bit here as well, I have to agree with that. I will test and see what happens with a few network connections but I have a bad feeling about this one also. I don't think it will get much better and not even close to a non multi thread implementation, but further tests will show. I am just curious if anyone could run the same tests in some other OS and maybe compare some notes. I looked around but the few things I found are doubtful. There are no good performance tests between threads and no threads with random access reads and writes, or maybe even better MyISAM and InnoDB as well as threads/no threads. This should also be interesting due to the locking differences of those two databases among other things. Lefteris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
[EMAIL PROTECTED] wrote:
Claire Lee [EMAIL PROTECTED] wrote on 09/27/2005 03:48:11 PM:
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
So you want to sort by secname except when secname starts with 'SWAP'
ORDER BY secname
, if (secname like 'swap%'
,(right(secname, length(secname)-locate('p', secname))+0)
,0)
, date;
by giving every *other* entry a default second sort-by of 0, they end up
all sorting according to secname then date. It's when secname starts with
swap that you get the sub-sorting value according to the end of the
string. Make sense?
If secname is like 'swap%', why are you then using locate to find the p
when it has to be the 4th letter or secname wouldn't be like 'swap%'.
Also if your first order by argument is secname how is the second
argument going to do anything since swap10 and swap2 are different the
first argument is all you need to uniquely identify them.
--
Chris W
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
Claire Lee wrote: This simplified my second expression in the if statement. Thank you. But the query still doesn't sort by the numbers, here's the result: mysql select distinct secname, date from optresult where secname like 'swap%' a nd date like '2005-09-2%' order by if (secname like 'swap%',abs(substring(secnam e,5)), secname); SELECT DISTINCT secname , date FROM optresult WHERE secname LIKE 'swap%' AND date LIKE '2005-09-2%' ORDER BY ABS(SUBSTRING(secname,5)) You don't need the IF in the ORDER BY -- that's already been constrained by your WHERE ... +--++ | secname | date | +--++ | SWAP0.25 | 2005-09-21 | | SWAP0.5 | 2005-09-21 | | SWAP1| 2005-09-21 | | SWAP10 | 2005-09-26 | | SWAP10 | 2005-09-23 | | SWAP10 | 2005-09-21 | | SWAP2| 2005-09-26 | | SWAP2| 2005-09-23 | | SWAP2| 2005-09-22 | | SWAP2| 2005-09-21 | | SWAP3| 2005-09-21 | | SWAP3| 2005-09-26 | | SWAP3| 2005-09-23 | | SWAP3| 2005-09-22 | | SWAP5| 2005-09-21 | | SWAP5| 2005-09-26 | | SWAP5| 2005-09-23 | | SWAP5| 2005-09-22 | +--++ HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To multi thread or NOT to multi thread?
Hello again,
I modified your threading code to use a thread pool. Here are my results:
pooled-threading.c:
[EMAIL PROTECTED]:~$ gcc -lmysqlclient_r -lpthread pooled-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.068s
user 0m0.041s
sys 0m0.097s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.088s
user 0m0.036s
sys 0m0.098s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.107s
user 0m0.036s
sys 0m0.100s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.068s
user 0m0.043s
sys 0m0.102s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.067s
user 0m0.044s
sys 0m0.088s
[EMAIL PROTECTED]:~$
poor-threading.c (your original threading):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient_r -lpthread poor-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.118s
user 0m0.026s
sys 0m0.069s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.110s
user 0m0.018s
sys 0m0.049s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.110s
user 0m0.029s
sys 0m0.050s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m0.109s
user 0m0.029s
sys 0m0.054s
[EMAIL PROTECTED]:~$
no-threading.c (your original as well):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient no-threading.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.096s
user 0m0.023s
sys 0m0.032s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.095s
user 0m0.012s
sys 0m0.038s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.095s
user 0m0.019s
sys 0m0.028s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m0.094s
user 0m0.015s
sys 0m0.034s
[EMAIL PROTECTED]:~$
I ran these on linux 2.6, my db server is not localhost but another server
on the same network. As you can see the pooled threading (I had 25
connections in the pool) was the fastest as far as real-time. Part of this
might be because it utilizes network bandwith better. It did however use
more actual cpu time than the single threaded implementation, but usually
what you really care about is real-time anyway. Apart from that I'd say that
testing with 100 thread each doing one query is silly as you incur the
thread creation/initialization overhead once per query. A better test is to
have 100 threads do 100 queries in a row or something, vs a single thread
doing 1 queries. Here are my results for doing that with the same
implementations (I dropped your threading implementation as I think we've
established its not the winner):
pooled-threading2.c:
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.380s
user 0m2.487s
sys 0m5.761s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.373s
user 0m2.602s
sys 0m5.720s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.406s
user 0m2.503s
sys 0m5.670s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe ON
real 0m3.403s
user 0m2.472s
sys 0m5.698s
no-threading2.c (yours modified to do 100*100 instead of just 100):
[EMAIL PROTECTED]:~$ gcc -lmysqlclient no-threading2.c
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m9.085s
user 0m1.404s
sys 0m3.377s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.961s
user 0m1.436s
sys 0m3.313s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.937s
user 0m1.461s
sys 0m3.253s
[EMAIL PROTECTED]:~$ time ./a.out
Thread Safe OFF
real 0m8.977s
user 0m1.419s
sys 0m3.291s
[EMAIL PROTECTED]:~$
As you can see the same differences get more exaggerated here, there is CPU
overhead to do threading, but the real-time does decrease by using it.
Here is the code for pooled-threading2.c:
/*** MULTI THREADED EXAMPLE CODE ***/
/ -lmysqlclient_r -lpthread /
#include stdarg.h
#include stdio.h
#include stdlib.h
#include string.h
#include pthread.h
#include mysql/mysql.h
#define MAX 100
#define CONNECTIONS 25
typedef struct db_donfig {
char host[16];
char user[16];
char pass[16];
char name[16];
unsigned int port;
char *socket;
} db_config;
typedef struct db_mutex {
MYSQL *db;
pthread_mutex_t lock;
} db_mutex;
db_mutex dbm[CONNECTIONS];
void *db_pthread(void *arg);
static void db_die(MYSQL *db, char *fmt, ...);
MYSQL *db_connect(MYSQL *db, db_config *dbc);
void db_disconnect(MYSQL *db);
long db_query(MYSQL *db, pthread_mutex_t *lock, const char *query);
int main(int argc, char **argv) {
int i;
pthread_t pthread[MAX];
db_config dbc;
my_init();
strcpy(dbc.host,devdb01);
strcpy(dbc.user,scopeuser);
strcpy(dbc.pass,gosonicsalpha);
strcpy(dbc.name http://dbc.name,);
dbc.port = 3306;
dbc.socket = NULL;
for(i=0; iCONNECTIONS; ++i) {
dbm[i].db = db_connect(dbm[i].db, dbc);
pthread_mutex_init(dbm[i].lock, NULL);
}
if (!mysql_thread_safe())
fprintf(stderr, Thread Safe OFF\n);
else
fprintf(stderr, Thread Safe ON\n);
// pthread_setconcurrency(4);
// fire up the threads
for (i = 0; i MAX; ++i)
pthread_create(pthread[i], NULL, db_pthread, (void *)(i%CONNECTIONS));
// wait for threads to finish
for (i = 0; i MAX; ++i)
pthread_join(pthread[i],
Re: linux timestamp
On 9/27/05, Nigel Wood [EMAIL PROTECTED] wrote:
Dotan Cohen wrote:
Hi all, I have a field in a mysql database v4.0.18 that contains a
linux timestamp. I have been googleing for a solution that would
return to me all the entries where the timestamp falls on, say a
wednesday, or between 2pm to 3pm. I am led to believe that it is
possible, but I have found no examples. Something like:
SELECT * from listings WHERE timestamp(day==wednesday)
or
SELECT * from listings WHERE timestamp(14:00 = time = 15:00)
Fair warning: Because MySQL won't be able to make proper use of it's
indexes the following queries will be VERY slow with any reasonable
sized data set. If your going to be performing these queries often I'd
recommend either storing the field as a datatime (you can do date time
to unixtime conversion in MySQL using the unix_timestamp() function) or
denormalising the data and storing both.
SELECT * from listings WHERE date_format('%W',from_unixstamp(timestamp)) =
'Wednesday';
SELECT * from listings WHERE cast(
date_format('%H',from_unixstamp(timestamp)) as unsigned) between 14 and 15;
Thank you everyone. I'll set up the day and hour fields. It is a big
database, and I will be accessing often. I especially appreciated the
link to the proper chapter in the manual, as I am not as familiar with
the mysql manual as I probably should be.
Thanks.
Dotan Cohen
http://lyricslist.com/lyrics/artist_albums/389/osbourne_ozzy.php
Osbourne, Ozzy Song Lyrics
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recommendations for memory use with 16GB
I'm moving from 32-bit intel to the brave new world of AMD64. Our new servers are dual Opterons with 16GB of RAM. We will be running mysql 4.1.14-standard on redhat enterprise linux 4 x86_64. Since I'm new to this, what's my best bang-for-buck in setting up mysql's memory usage? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL LINT / Schema checker?
We use SVN to commit our code, but sometimes a dev will commit broken SQL schema. We use php -l to validate all php prior to allowing the checkin, wondering if there is some simmilar tool for mySQL (we're migrating to v5.0 if that helps) to prevent broken schema syntax from sneaking into our repository? A google search didn't seem to turn up much, surprisingly. :( D.Vin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining data from two databases
Hi, I have one database that I would like to split into two.. Well actually my client does.. I don't think we should. But, is there a way to join data from the seperated databases? One table authors will be connected to a table called contacts each residing in different databases on the same server. Thanks for any help :Ant -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.8/113 - Release Date: 9/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining data from two databases
Anthony Brown wrote: I have one database that I would like to split into two.. Well actually my client does.. I don't think we should. But, is there a way to join data from the seperated databases? One table authors will be connected to a table called contacts each residing in different databases on the same server. SELECT ... FROM db1.table1 LEFT JOIN db2.table2 WHERE ... ? -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
Will someone please respond to this? - Ed Ed Reed [EMAIL PROTECTED] 9/22/05 1:30 PM Bump! Ed Reed [EMAIL PROTECTED] 9/21/05 2:09:58 PM I just upgraded from 4.1.11 to 4.1.14a and now Group_Concat returns garbage. In the previous version Group_Concat returned a text string and after the upgrade they are returning a blob.
