Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No
Gleb Paharenko schrieb: Hello. I've looked through the bug database, and the only thing that I've found was an already-closed bug: http://bugs.mysql.com/bug.php?id=6148 I had been looking around the Changelogs, but I had not found that one. Sounds pretty much like my problem :( But I use 4.1.7, not 4.0.21 ...weird. Check that your server passes rpl_relayspace.test. Go to the mysql-test directory and execute: ./mysql-test-run t/rpl_relayspace.test This one runs wirhout errors on the master and the slave...: hostname:/usr/local/mysql-standard-4.1.7-pc-linux-i686-icc-glibc23/mysql-test# ./mysql-test-run t/rpl_relayspace.test Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TESTRESULT --- rpl_relayspace [ pass ] --- Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished All 1 tests were successful. I'm not able to exchange the mysql-software itself (I use the icc-binary) to a gcc-version or to upgrade to 4.1.9 in the next 2-3 weeks. And looking at the changelogs on mysql.com I don't think it would change anything... Hasn't anybody else had such problems with 4.1.x? hostname:/usr/local/mysql-standard-4.1.7-pc-linux-i686-icc-glibc23/bin# ./mysqld --version ./mysqld Ver 4.1.7-standard for pc-linux on i686 (Official MySQL-standard binary) (more detailed information on my systems in my initial mail from 2005-1-27) btw: I also ran mysqlcheck -q and mysqlcheck -o on all tables last week to make sure the tables are OK... Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, My problem still goes on... After having had the problem 2 more times within 1 day, I decided to re-do the replication (copy the whole database onto the slave with rsync and reset master and slave). That only lasted for little more than 1 day and I ended up with the same error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I can look at the binlog with mysqlbinlog on the master and the slave; no errors or problems. After a simple SLAVE START without having done any changes to the database, the slave thread startet again and caught up with the master. I've been using mysql's replication-feature since it first came up in 1999 or 2000 and dealt with lots of problems and workarounds, but this one is weird. Any ideas anybody? Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Non root user MySQL install possible?
Hi. Before asking my question I would like to state that I have looked through the installation documentation, and have also successfully performed MySQL installations on WindowsXP (along with Apache and PHP), as well as on Linux machines that I have administrative (root) access on. However, I am having problems installing MySQL on a Linux machine as a non root user. My question is: is it possible? I don't see why it wouldn't be, but after fighting with setting the correct paths in mysql.server, and making sure ~/.my.cnf looked right, I am having doubts. Rather than list every error I ran into, I will simply state that I had problems setting up the proper paths (such as not being able to alter /var/run/mysql/mysql.pid, which obviously cannot be done while not root). If it is possible to do a local install as a non privileged user, can someone please point me in the right direction, to a guide or something? Or, simply state which config files need to be edited (~/.my.cnf, mysql.server, etc) and I will work from there. Google hasn't helped much/I am not using the right search terms. Thank you. Also - if it helps, I am trying to install both the client and the server so I can access the MySQL database via Apache/PHP. The client should already be set to go, it is getting the server up and running that I am having problems with. Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No
Hello. But I use 4.1.7, not 4.0.21 ...weird. As said at: http://dev.mysql.com/doc/mysql/en/news-4-1-8.html Fixed a bug which caused a crash when only the slave I/O thread was stopped and started. (Bug #6148) I suggest you to upgrade to the latest release (4.1.9 now). Jan Kirchhoff [EMAIL PROTECTED] wrote: Gleb Paharenko schrieb: Hello. I've looked through the bug database, and the only thing that I've found was an already-closed bug: http://bugs.mysql.com/bug.php?id=6148 I had been looking around the Changelogs, but I had not found that one. Sounds pretty much like my problem :( But I use 4.1.7, not 4.0.21 ...weird. Check that your server passes rpl_relayspace.test. Go to the mysql-test directory and execute: ./mysql-test-run t/rpl_relayspace.test This one runs wirhout errors on the master and the slave...: hostname:/usr/local/mysql-standard-4.1.7-pc-linux-i686-icc-glibc23/mysql-test# ./mysql-test-run t/rpl_relayspace.test Installing Test Databases Removing Stale Files Installing Master Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb Installing Slave Databases running ../bin/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TESTRESULT --- rpl_relayspace [ pass ] --- Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished All 1 tests were successful. I'm not able to exchange the mysql-software itself (I use the icc-binary) to a gcc-version or to upgrade to 4.1.9 in the next 2-3 weeks. And looking at the changelogs on mysql.com I don't think it would change anything... Hasn't anybody else had such problems with 4.1.x? hostname:/usr/local/mysql-standard-4.1.7-pc-linux-i686-icc-glibc23/bin# ./mysqld --version ./mysqld Ver 4.1.7-standard for pc-linux on i686 (Official MySQL-standard binary) (more detailed information on my systems in my initial mail from 2005-1-27) btw: I also ran mysqlcheck -q and mysqlcheck -o on all tables last week to make sure the tables are OK... Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, My problem still goes on... After having had the problem 2 more times within 1 day, I decided to re-do the replication (copy the whole database onto the slave with rsync and reset master and slave). That only lasted for little more than 1 day and I ended up with the same error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I can look at the binlog with mysqlbinlog on the master and the slave; no errors or problems. After a simple SLAVE START without having done any changes to the database, the slave thread startet again and caught up with the master. I've been using mysql's replication-feature since it first came up in 1999 or 2000 and dealt with lots of problems and workarounds, but this one is weird. Any ideas anybody? Jan -- 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: I want to get schema infomation in MySQL DB for schema.
Hello. The MySQL server have INFORMATION_SCHEMA tables, but they are implemented in MySQL 5.0.2 and not production ready yet. See: http://dev.mysql.com/doc/mysql/en/information-schema.html For obtaining information about database metadata use different SHOW statements. See: http://dev.mysql.com/doc/mysql/en/charset-show.html ninjajs [EMAIL PROTECTED] wrote: Hi. ORACLE DBMS have a information for table or column in database dictionary, such as, USER_TAB_COLUMNS, USER_TABLES. Don't have any data base dictionary in MySQL, as ORACLE? thank you. -- 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: missing my.cnf file?
Hello. The MySQL server can be started without a configuration file, only with command line options. You can manually specify the location of my.cnf file with --defaults-file=path command line option. See: http://dev.mysql.com/doc/mysql/en/program-options.html sol beach [EMAIL PROTECTED] wrote: I have inhertited this old Solaris x86 system with MYSQL installed on it. mdb:/tmp 15:38:08 [539]# uname -a SunOS mdb.hitbox.com 5.7 Generic_106542-24 i86pc i386 i86pc I would rate myself as an advanced novice WRT MYSQL To a large deree this system is running OK. However, it does seem to be smacking into the limit of 100 concurrent connections. AFAIK, no /etc/my.cnf file no $HOME/.my.cnf file exist. I've looked looked at the startup scripts do not see any --config-file qualifier be specified. datadir=/a/mysqldata/ and there is no *cnf file in this folder either # find / -name \*cnf -ls 941333 -rw-rw 1 root dba 2179 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-small.cnf 941343 -rw-rw 1 root dba 2464 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-medium.cnf 941353 -rw-rw 1 root dba 2480 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-large.cnf 941363 -rw-rw 1 root dba 2502 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-huge.cnf 3826293 -rw-r--r-- 1 root dba 2179 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-small.cnf 3826303 -rw-r--r-- 1 root dba 2464 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-medium.cnf 3826313 -rw-r--r-- 1 root dba 2480 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-large.cnf 3826323 -rw-r--r-- 1 root dba 2502 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-huge.cnf 6962157 -rw-r--r-- 1 root other7146 Jul 11 2002 /a/local/ssl/openssl.cnf I may be wrong but I am convinced that MYSQL is reading some file to acquire values for its varialbles. How do I identify which file it is using? -- 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]
SQLSTATE mapping problem
Hi all, We are using MySQL ODBC 3.51 driver for db connection. When executing an SQL statement using SQLExecDirect() function and if there is a syntax error in the SQL statement, the SQLSTATE returned by the driver is 23000 (which stands for invalid constraint/duplicate keys.) instead of 42000 (which stands for syntax error or access violation) . Like this we are getting different SQLSTATE values for other errors. Can anybody suggest what is going wrong with this? We are using SQLGetDiagRec() method to fetch the native error code, Error message and the SQLSTATE. Also we are not able to find the native error code list for the MySQL ODBC 3.51 driver. Can anyone please suggest a clue? Regards Narasimha 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: Help with a query using multiple LEFT JOINS
Thanks Tom Doing as you suggested as well as applying the st.group = 'B' in the tbl4 ON achieved what I wanted. Graham -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: 01 February 2005 00:03 To: Graham Cossey Cc: mysql@lists.mysql.com Subject: RE: Help with a query using multiple LEFT JOINS If you mean that you want to get a row even if tbl2 does not have a matching row for dcode, then move the conditions into the ON clause. Example based off of what you had: SELECT FROM tbl1 as d LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE d.status!='X' AND d.region='1A' AND st.group = 'B' GROUP BY d.dcode, r.code You may want to do the same for tbl4 depending on the behavior you are looking for. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Graham Cossey Sent: Monday, January 31, 2005 5:48 PM To: mysql@lists.mysql.com Subject: Help with a query using multiple LEFT JOINS I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
Hi, all I hope somebody can help me. Situation Three tables 1. ++++ | id | name | region | ++++ | 13 | Name1 | 1 | | 15 | Name2 | 2 | | 47 | Name3 | 1 | | 57 | Name4 | 2 | | 65 | Name5 | 2 | | 77 | Name6 | 1 | | 85 | Name7 | 1 | ++++ 2. ++++--+ | id | Date | amount1| current | ++++--+ | 13 | 21.7.1967 | 9900 |N | | 13 | 21.7.1968 | 9800 |J | | 57 | 11.9.1999 | 12800 |J | | 65 | 24.6.1991 | 1200 |N | | 65 | 21.7.1967 | 1275 |J | | 85 | 14.2.2001 | 45000 |J | ++++--+ 3. ++---++ | id | Year | amount2| ++---++ | 13 | 1967 | 100 | | 13 | 1968 | 100 | | 13 | 1969 | 125 | | 15 | 1967 | 200 | | 15 | 1968 | 220 | | 15 | 1969 | 220 | | 47 | 1967 | 500 | | 47 | 1968 | 580 | | 47 | 1969 | 550 | ++---++ In table 1 there are all IDs. In tables 2 and 3 there can be some IDs. As you see, in region 1 there are the IDs 13,47,77 and 85. In table 2 only 13 and 85, in table 3 only 13 and 47. What I would like to have is a result like this: ++---+--+ | region | sum(amount1) if current=J | sum(amount2) | ++---+--+ | 1| 54800 | 1630 | | 2| 14075 | 640 | ++---+--+ Many thanks in advance Joachim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi! I have a problem with a query. I have a given amount of values (id:s) (for example 1,2,6,12,77,78,79,122,123,124), these are related to a table, pref, and belongs to a specific category, pref_cat-table. So, 1,2,6,12 are connected to id 1 in pref_cat, 77,78,79 to id 2 and 122,123,124 to id 3 in pref_cat. Table: pref | id| bigint(20) unsigned | | user_id | bigint(20) unsigned | | category_id | bigint(20) unsigned | | option_id | bigint(20) unsigned | Each user have X nr of rows in this table. From the given id:s I want match them with this table in the way that one id (option_id) from the given values in each category_id (ex 1,2 and 3) should be in this table. So, if a user X have id 1, 77, 122 in this table he should be a match. Below I have query that doesn't work, the reason it doesn't work is because it tries to match to the same id in pref. SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND p.option_id IN ( 1,2,6,12 ) AND p.option_id IN ( 77,78,79) AND p.option_id IN ( 122,123,124 ) What I rather want to do is to match all p.option_id:s for the specific user with the ones in the IN. For example if a user have the id: 1, 77 AND 122 I want to to a match like this: SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND if any of (1,77,122) exist IN (1,2,6,12) AND if any of (1,77,122) exist IN (77,78,79) AND if any of (1,77,122) exist IN (122,123,124 ) Is this in some way possible to do or does someone have an other solution in mind? (Maybe I should tell you that I'm using mySQL 4.1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Non root user MySQL install possible?
-Original Message- From: j [mailto:[EMAIL PROTECTED] Sent: dinsdag 1 februari 2005 10:09 To: mysql@lists.mysql.com Subject: Non root user MySQL install possible? Hi. Before asking my question I would like to state that I have looked through the installation documentation, and have also successfully performed MySQL installations on WindowsXP (along with Apache and PHP), as well as on Linux machines that I have administrative (root) access on. However, I am having problems installing MySQL on a Linux machine as a non root user. My question is: is it possible? I don't see why it wouldn't be, but after fighting with setting the correct paths in mysql.server, and making sure ~/.my.cnf looked right, I am having doubts. Rather than list every error I ran into, I will simply state that I had problems setting up the proper paths (such as not being able to alter /var/run/mysql/mysql.pid, which obviously cannot be done while not root). If it is possible to do a local install as a non privileged user, can someone please point me in the right direction, to a guide or something? Or, simply state which config files need to be edited (~/.my.cnf, mysql.server, etc) and I will work from there. My first reaction was: I have never even heard of MySQL running as root! and I am sticking with that. :) Seriously, you should never run MySQL as root. Create a user, say, mysql, chown mysql:mysql /var/db/mysql/* (or wherever your databases reside), chmod go-rwx /var/db/mysql/*, add user = mysql to your my.cnf, and you're pretty much done. Granted, you cannot have your pid in /var/run/ directly. So? Create a subdir under /var/run/, owned by the mysql user, and have the pid in there. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non root user MySQL install possible?
Sorry, I don't mean running MySQL as root, I mean I do not have root access on the server I am trying to install MySQL on - for myself, under my home directory. I run into permission problems (because I am not root, I can't alter anything under /var). I am trying to edit the necessary configs so the paths point to /home/me but that is where I am running into problems. What I'm looking for is a guide, or a list of config files that need to be edited. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a query using multiple LEFT JOINS
In article [EMAIL PROTECTED], Graham Cossey [EMAIL PROTECTED] writes: I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? For no matching entries in tbl2, r.* would be NULL, and that's something you exclude in your WHERE clauses (r.mcode='AB' etc). Depending on what you want, you might include those conditions in the ON clause of your LEFT JOIN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Non root user MySQL install possible?
-Original Message- From: j [mailto:[EMAIL PROTECTED] Sent: dinsdag 1 februari 2005 11:20 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Non root user MySQL install possible? Sorry, I don't mean running MySQL as root, I mean I do not have root access on the server I am trying to install MySQL on - for myself, under my home directory. Ok, gotcha. I run into permission problems (because I am not root, I can't alter anything under /var). I am trying to edit the necessary configs so the paths point to /home/me but that is where I am running into problems. What I'm looking for is a guide, or a list of config files that need to be edited. Thanks. Hmm, I do not think it will be possible for you to actually compile MySQL this way; for it will undoubtledly want to install libraries, etc, in /usr/local/lib/ and such. But you can always just download a precompiled package from the MySQL download site, untar it, and run the binary straight from your home dir. :) The server you are doing this on may already have a MySQL server running. So, you would have to use something different from port 3306, or /tmp/mysql.sock, or /var/db/mysql/. But all of that is configurable. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non root user MySQL install possible?
Hello. If it is possible to do a local install as a non privileged user, can someone please point me in the right direction, to a guide or something? Yes it is possible. Here is a part of my configuration file. [client] port= 3718 socket = /home/gleb/mysqls/tmp/mysql.sock.gleb.a [mysqld] log_warnings=1 set-variable=user=gleb default_character_set=utf8 port= 3718 socket = /home/gleb/mysqls/tmp/mysql.sock.gleb.a skip-locking pid-file= /home/gleb/mysqls/tmp/mysql.pid.gleb.a log = /home/gleb/mysqls/logs/mysql.log.a log-bin = /home/gleb/mysqls/logs/mysql.log-bin.a log-update = /home/gleb/mysqls/logs/mysql.log-update.a log-error = /home/gleb/mysqls/logs/mysql.log-error.a log-isam= /home/gleb/mysqls/logs/mysql.log-isam.a datadir =/home/gleb/mysqls/mysql-debug-4.1.9-pc-linux-gnu-i686/data key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K wait_timeout=1000 interactive_timeout=50 server-id = 1 innodb_data_home_dir=/home/gleb/mysqls/mysql-debug-4.1.9-pc-linux-gnu-i686/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir =/home/gleb/mysqls/mysql-debug-4.1.9-pc-linux-gnu-i686/data innodb_log_arch_dir =/home/gleb/mysqls/mysql-debug-4.1.9-pc-linux-gnu-i686/data innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 j [EMAIL PROTECTED] wrote: Hi. Before asking my question I would like to state that I have looked through the installation documentation, and have also successfully performed MySQL installations on WindowsXP (along with Apache and PHP), as well as on Linux machines that I have administrative (root) access on. However, I am having problems installing MySQL on a Linux machine as a non root user. My question is: is it possible? I don't see why it wouldn't be, but after fighting with setting the correct paths in mysql.server, and making sure ~/.my.cnf looked right, I am having doubts. Rather than list every error I ran into, I will simply state that I had problems setting up the proper paths (such as not being able to alter /var/run/mysql/mysql.pid, which obviously cannot be done while not root). If it is possible to do a local install as a non privileged user, can someone please point me in the right direction, to a guide or something? Or, simply state which config files need to be edited (~/.my.cnf, mysql.server, etc) and I will work from there. Google hasn't helped much/I am not using the right search terms. Thank you. Also - if it helps, I am trying to install both the client and the server so I can access the MySQL database via Apache/PHP. The client should already be set to go, it is getting the server up and running that I am having problems with. Thanks again. -- 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]
Premature InnoDB conversion.
MySql 4.0.14 We tried to convert MyISAM table format to INNODB format, it took forever to finish the process, someone intervened and killed the process through Task Manager. Now can't run Mysqld-nt, running it with the --console reports the followings. Your help is appreciated. Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp. c:\MySql\mysqld-nt --console 050201 11:26:22 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 799702164 050201 11:26:22 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 050201 11:26:24 InnoDB: Assertion failure in thread 1344 in fi le ../innobase/include\page0page.ic line 482 InnoDB: Failing assertion: offs UNIV_PAGE_SIZE InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create index if not exists????
I'm writing an install script and need to create an index if it does not exists. I've tried SQL statements but none of them work. Basically I'm looking to do something like $sql[] = ALTER TABLE `exp_members` ADD INDEX IF NOT EXIST `idx_members_username` ( `username` ); Is this possible? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tables damaged
Hi, Snort had had problems to insert data into the table so, at i opened a connection to the server and dit: use snort; i got this error: Din't find any fields in table 'data' Din't find any fields in table 'event' Din't find any fields in table 'tcphdr' Database changed With phpmyadmin these tables are marked in use What can i do to get this back to work, had no time to create a backup script witch i schall create as fast as possible now. Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 x latin
Thanks for your help Gabriel. I will use the latin1 with case sensitive. Andre On 1/31/05 7:32 AM, Gabriel PREDA [EMAIL PROTECTED] wrote: I think you should use: latin1_general_ci and on columns with spanish you should use latin1_spanish_ci. English, French, Portuguese not having a separate collation I believe that they are included into latin1_general_ci ? Please observe the last part from a collation name: case sensitive (_cs) ... case insensitive (_ci) ... and binary (_bin) ! UTF8 consumes more space on disk ! If you issue a SHOW CHARACTER SET command mysql SHOW CHARACTER SET; You will notice the last column named Maxlen that defines how many bytes takes to store a letter. On utf8_general_ci you will notice 3... as on latin1__xx you will only see 1... Although these values represent a MAX value... what you need will use at most 2 bytes per letter in UTF-8... but in latin1_xxx_xx will, for sure, will use one byte... on long texts this will count ! In my opinion you should stick to latin1_xxx_xx ! Afther some thought and aditional computation I declared myself satisfied with: latin2 - ISO 8859-2 Central European - latin2_general_ci - 1byte but I'm having Romanian + English + French columns. Try to read http://dev.mysql.com/doc/mysql/en/charset.html with no distraction arround you ! Gabriel - Original Message - From: Andre Matos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, January 30, 2005 9:29 PM Subject: utf8 x latin Hi list, I was wondering which one is better to use, utf8_general_ci or latin1_general_cs, with English, French, Portuguese, and Spanish? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-query-browser and SuSE 8.2
Hi: I have not been able to find a precompiled version of mysql-query-browser for SuSE 8.2, either at mysql web site, or using google. Compiling from source fails because of version differences in libxml-2.0, for instance. Anybody know of a URL where I can find a version that will work with SuSE 8.2? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What are the reasons?
Hi everybody, What are the possible reasons for runaway MySQL process on Sun Solaris, beyond joins on fields with NULLs. Regards, Mikhail Berman
RE: SQLSTATE mapping problem
Hi all, Can anybody suggest whether it is due to version incompatibility?? We are using MySQL 4.0.21 and MySQL ODBC Driver 3.51. Thanks in advance Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 2:53 PM To: mysql@lists.mysql.com Subject: SQLSTATE mapping problem Hi all, We are using MySQL ODBC 3.51 driver for db connection. When executing an SQL statement using SQLExecDirect() function and if there is a syntax error in the SQL statement, the SQLSTATE returned by the driver is 23000 (which stands for invalid constraint/duplicate keys.) instead of 42000 (which stands for syntax error or access violation) . Like this we are getting different SQLSTATE values for other errors. Can anybody suggest what is going wrong with this? We are using SQLGetDiagRec() method to fetch the native error code, Error message and the SQLSTATE. Also we are not able to find the native error code list for the MySQL ODBC 3.51 driver. Can anyone please suggest a clue? Regards Narasimha 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. 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
flush-hosts/tcpwrapper problems
Hi, I've got an odd setup that I need to diagnose. The customer won't allow us to upgrade very far with the OS or MySQL. Its currently on a BSD/OS 4.0.1 system, and 4.2 is the highest we could possibly be allowed to go. We have upgraded to mysql-3.23.58 (Customer won't let us go to 4 or 5), with the following patches from the RedHat distro: mysql-3.23.58-config.patch mysql-3.23.58-setpermission.patch mysql-3.23.58-dropdb.patch mysql-3.23.58-symlink.patch mysql-3.23.58-hotcopy.patch mysql-3.23.58-typo.patch mysql-3.23.58-security.patch The configure is just ./configure --with-libwrap. (We do have to go in to client/Makefile, mysys/Makefile and sql/Makefile to take the -fno-exceptions out otherwise it won't compile) With the recent reports of an issue with MySQL we've been getting alot of probes against the server. Its currently set to only allow connections from a monitoring machine, and locally. It appears that after a while the server starts to get QUITE slow... Requests take over a minute, even using the local socket. Until we do a flush-hosts, it runs quite slow. Has anyone heard/seen/experienced this? On some other customer servers, we just had to put mysqld:ALL into /etc/hosts.allow to prevent the database from becoming unusable. Thanks, Tuc/TTSG Internet Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
passing environment variable to an SQL script
Hi everybody. I do a lot of maintenance things on the DB by piping SQL files to my MySql DB. What would be very useful is to find a way of passing very simple *arguments* to these scripts. The only way I can think of it is using a nasty trick mysql --set-variable=wait_timeout=1234 file.sql and file.sql contains the line: SELECT @VAR:=@@wait_timeout; This lets me smuggle a variable into my SQL script. Has anyone got a cleaner way? Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables damaged
Hello. If your tables are MyISAM and you think that they are damaged, I suggest you to stop the MySQL server, make a backup of the data directory (just copy to other place) and then run a myisamchk program to repair them. See: http://dev.mysql.com/doc/mysql/en/repair.html http://dev.mysql.com/doc/mysql/en/crash-recovery.html Patrick Marquetecken [EMAIL PROTECTED] wrote: Hi, Snort had had problems to insert data into the table so, at i opened a connection to the server and dit: use snort; i got this error: Din't find any fields in table 'data' Din't find any fields in table 'event' Din't find any fields in table 'tcphdr' Database changed With phpmyadmin these tables are marked in use What can i do to get this back to work, had no time to create a backup script witch i schall create as fast as possible now. Patrick -- 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: Column Permissions?
I have seen this type of security implemented through multiple VIEWs but not directly by the storage engine. You will need to create a view for your user that exposes only out those rows designated for that user. The drawback to this is that you will need to issue one view for each user or group of users and assign rights appropriately. Another drawback is that your version of MySQL may not support views. Please read http://dev.mysql.com/doc/mysql/en/create-view.html for the details of creating views and http://dev.mysql.com/doc/mysql/en/grant.html for instructions on how to set permissions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thomas Matthews [EMAIL PROTECTED] wrote on 02/01/2005 02:47:00 AM: Hello, I am wondering if it is possible to limit privileges for a user account based on information stored within specific columns. For example, I plan to include a `username` column on a table with the hope of having each user log into the database using their own credentials and only be able to INSERT, UPDATE, SELECT, DELETE rows where their username is what the `username` column is populated with. Keep in mind that the users will have access to issue any query they wish against the database so I would like to restrict this at the server level. Thank you in advance. -- Thomas Matthews [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT on string field
Hi, I've a field with www.s.com/p.php?id=3 www.s.com/p.php?id=4 www.s.com/p.php?id=5 if a do a query with the atribute 'group by' i want that the system regroup all that is after the '?' is that possible? SELECT field WHERE all that is after the '?' GROUP BY all that is after the '?' the result should be: www.s.com/p.php thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT on string field
[snip]
I've a field with
www.s.com/p.php?id=3
www.s.com/p.php?id=4
www.s.com/p.php?id=5
if a do a query with the atribute 'group by' i want that the system
regroup
all that is after the '?'
is that possible?
SELECT field WHERE all that is after the '?' GROUP BY all that is after
the
'?'
the result should be:
www.s.com/p.php
[/snip]
SELECT substring(field, 1, 15) FROM table WHERE substring(field, 16) =
'?' GROUP BY field
should return what you have asked for above (www.s.com/p.php). However,
if you group by each thing that is after the '?' your result set would
be
www.s.com/p.php (this group is id=3)
www.s.com/p.php (this group is id=4)
www.s.com/p.php (this group is id=5)
You can also try
SELECT substring(field, 1, locate('?', field)-1) FROM table GROUP BY
substring(field, locate('?', field));
http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html
http://dev.mysql.com/doc/mysql/en/string-functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT on string field
Hi Arcangelo! Try this: SELECT COUNT(*), url, SUBSTRING_INDEX(url, ?, 1) AS page_name FROM sites GROUP BY page_name SUBSTRING_INDEX will return the leftmost (or rightmost) part of a string before the ocurrence of a delimiter (? in this case. See Documentation: Chapter String Functions). The trick here is to GROUP BY the result of this operation. Note that you could elaborate this further so that you could GROUP by the host name. You will need to anidate SUBSTRING_INDEX calls so that you end up with the desired data. For example: SELECT COUNT(*), url, SUBSTRING_INDEX(SUBSTRING_INDEX(url, ?, 1), /, 1) AS host_name FROM sites GROUP BY host_name This will group together the following URIs as one: - www.domain.com/pageone.php(There is no ?) - www.domain.com?id=2 (There is no /) Beware though, this usage will not use indexes on url column so be careful and do not use this on big tables. You will need to use more elaborated techniques to speed up query times on big tables. Cheers, Jose Miguel. - ORIGINAL MESSAGE --- I've a field with www.s.com/p.php?id=3 www.s.com/p.php?id=4 www.s.com/p.php?id=5 if a do a query with the atribute 'group by' i want that the system regroup all that is after the '?' is that possible? SELECT field WHERE all that is after the '?' GROUP BY all that is after the '?' the result should be: www.s.com/p.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How much is cached
Hi all. I'm implementing a filesystem that resides inside a MySQL database using FUSE[1]. The actual data of my files is stored in fields of type BLOB and what I want to know is: Does MySQL cache the contents of a BLOB in its buffer cache? What I mean is, if I do two consequtive calls to the database that select out the contents of a BLOB, will the BLOB be read only once (and found in cache the other time), or will it be read from disk twice? Best regards, Mads Kristensen [1] http://fuse.sf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
matt_lists wrote: matt_lists wrote: Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple tables, not all of them Definitly the problem, but I cant find the option to remove it If I replace it with the following it works fine DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX DIRECTORY='E:\\mysql\\data\\campbell\\' works great in test, wont work in production, the backup sql file is 20-60 gig depending on the site, there's no way I can search and replace that size a file Anybody know? Is there some option I'm not setting which causes the data directory and index directory to not be correct? Why are there not hundreds of people with this problem, I would think it affects anyone on 4.x running on a windows machine, I hope they dont learn the hard way when a restore fails to work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: flush-hosts/tcpwrapper problems
Hello. On some systems tcpwrapper works with MySQL not that well as expected. For example see: http://bugs.mysql.com/bug.php?id=5652 And some bugs aren't fixed in old 3.23.xx versions. In my opinion, your problem is related to DNS. You may disable DNS lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can only use IP numbers in the MySQL grant tables. See: http://dev.mysql.com/doc/mysql/en/dns.html Tuc [EMAIL PROTECTED] wrote: Hi, I've got an odd setup that I need to diagnose. The customer won't allow us to upgrade very far with the OS or MySQL. Its currently on a BSD/OS 4.0.1 system, and 4.2 is the highest we could possibly be allowed to go. We have upgraded to mysql-3.23.58 (Customer won't let us go to 4 or 5), with the following patches from the RedHat distro: mysql-3.23.58-config.patch mysql-3.23.58-setpermission.patch mysql-3.23.58-dropdb.patch mysql-3.23.58-symlink.patch mysql-3.23.58-hotcopy.patch mysql-3.23.58-typo.patch mysql-3.23.58-security.patch The configure is just ./configure --with-libwrap. (We do have to go in to client/Makefile, mysys/Makefile and sql/Makefile to take the -fno-exceptions out otherwise it won't compile) With the recent reports of an issue with MySQL we've been getting alot of probes against the server. Its currently set to only allow connections from a monitoring machine, and locally. It appears that after a while the server starts to get QUITE slow... Requests take over a minute, even using the local socket. Until we do a flush-hosts, it runs quite slow. Has anyone heard/seen/experienced this? On some other customer servers, we just had to put mysqld:ALL into /etc/hosts.allow to prevent the database from becoming unusable. Thanks, Tuc/TTSG Internet Services, Inc. -- 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]
Removing 3.23, installing 5.02
Hi folks, I've got a Fedora Core 3 box that came with MySQL 3.23.xx, recently updated to 3.23.58. I wanted to wipe the box clean and install 5.0, but the Add/Remove Packages (and the manual RPM command) both failed on multiple dependencies, so I (foolishly?) took matters into my own fat little fingers and tried removing all the MySQL files manually. I think I got them all except the ones in /sur/lib; 'rm' on mysqlbug and mysql_config failed with a 'cannot lstat - no such file or directory' message. Anyway, running [EMAIL PROTECTED] rpm -i MySQL-server-5.0.2-0.i386.rpm failed with warning: MySQL-server-5.0.2-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: MySQL conflicts with mysql-3.23.58-14.i386 MySQL-server conflicts with mysql-server-3.23.58-14.i386 And I am stuck. I can't find any more files having to do with 3.23 (except those lib files). Are those what's causing this problem? How do I get rid of them? Thanks, Whil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested query bug
Having a strange bug with nested queries SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); this works, but it should not there is no BLAH column in table C If I change it to this, it works correctly, as far as I can tell, still working on validating data SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); the first sql shouldnt even run, when you run the nested query alone, ie SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' it failes, column does not exist! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested query bug
matt_lists wrote: Having a strange bug with nested queries SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); this works, but it should not there is no BLAH column in table C If I change it to this, it works correctly, as far as I can tell, still working on validating data SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); the first sql shouldnt even run, when you run the nested query alone, ie SELECT BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' it failes, column does not exist! FYI I just verfied the data the bugged sql runs fine, but completely ignored the nested query, when the column name did not match the column in the first table not sure if this is a bug, feature, intended, but I know the work around now, alias the 2nd table column to make the name match the first table, and then it works fine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested query bug
[snip] SELECT A.*, b.* FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1 = 'c' AND c2= 'c' ); [/snip] This query is just badly formed, for instance, where is the table aliased 'b' in your from statement? Are you showing the complete query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld ended Can't get mysql running
Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786
Re: Nested query bug
Jay Blanchard wrote:
[snip]
SELECT A.*, b.*
FROM tablea a WHERE BLAH IN ( SELECT CORCOL BLAH FROM tableC WHERE c1
= 'c' AND c2= 'c' );
[/snip]
This query is just badly formed, for instance, where is the table
aliased 'b' in your from statement? Are you showing the complete query?
Here's the problem sql
SELECT 'SBC' FIN_RIC, BRAGG_ISS.* FROM BRAGG_ISS WHERE ARCHIVE_DATE =
DATE_SUB(CURDATE(),INTERVAL 5 DAY) AND SUBSTRING(DIC,1,2) = 'A5' AND
SUBSTRING(DIC,3,1) 'J' AND MRO = 'I' AND RIC_STOR IN (SELECT RIC_STOR
FROM DODAAF WHERE RIC_FIN_SP = 'SBC' AND CTASC = 'SBC' AND TYP_U_CD IN
('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J'))
There's no RIC_STOR in the DODAAF table, the column is called RIC
changing the sql to this works fine
SELECT 'SBC' FIN_RIC, BRAGG_ISS.* FROM BRAGG_ISS WHERE ARCHIVE_DATE =
DATE_SUB(CURDATE(),INTERVAL 5 DAY) AND SUBSTRING(DIC,1,2) = 'A5' AND
SUBSTRING(DIC,3,1) 'J' AND MRO = 'I' AND RIC_STOR IN (SELECT RIC
RIC_STOR FROM DODAAF WHERE RIC_FIN_SP = 'SBC' AND CTASC = 'SBC' AND
TYP_U_CD IN
('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J'))
the bug is, the nested query on the first statement is ignored
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested query bug
[snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested query bug
Jay Blanchard wrote: [snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement False should give no records, it's an IN () sense none match the condition instead it gives me every record?! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with SELECT SQL_CALC_FOUND_ROWS
Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Re: mysqld ended Can't get mysql running
We need more details in order to help: What operating system are you on? Which version of MySQL are you attempting to run? How are you attempting to start the MySQL server? Has it worked before or is this a new installation? What errors are being reported to your error log? The default location of the error logs is mysql installation folder/data/*.err We would love to help but your message is just not specific enough. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Debora Gomes.unitech [EMAIL PROTECTED] wrote on 02/01/2005 05:43:02 PM: Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786
Re: Nested query bug
matt_lists wrote: Jay Blanchard wrote: [snip] the bug is, the nested query on the first statement is ignored [/snip] No, it isn't ignored...it just returns a FALSE for the IN statement False should give no records, it's an IN () sense none match the condition instead it gives me every record?! Not only that, shoudlnt it say column does not exist? It does not return an error, it ignores the whole nested query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ut8 turkish character problem
I have mysql-4.1.9 installed. I am trying to insert and select turkish characters. My tables are utf8. I tried everything but not succesfull. turkish characters returns to ? or crops on turkish character. example: insert: Bingöl, select: Bing What is the secret of inserting turkish characters. I tried things like; SET NAMES 'utf8'; SET CHARACTER SET 'utf8'; But nothing solved problem. If anoybody knows how to insert and select turkish characters please help. PS. I tried using myODBC 2.5, 3.51.* all 3.51 versions but i got same result on all versions of myodbc. Also I tried with ASP and ASP.NET -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt Babineau [EMAIL PROTECTED] wrote on 02/01/2005 03:20:49 PM: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
ERROR 1071: Specified key was too long. Max key length is 500
Hi, I'm wondering if anybody can help me decrypt what this error code is referring to: ERROR 1071: Specified key was too long. Max key length is 500 This is the offending table creation script: CREATE TABLE `APPROVAL` ( `APPROVAL_ID` mediumint(9) unsigned NOT NULL auto_increment, `APPROVAL_CURRENT_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_HIGHEST_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_STATUS` varchar(255) NOT NULL default '', `APPROVAL_SUB_STATUS` varchar(255) default NULL, `APPROVAL_APPLICATION` varchar(255) NOT NULL default '', `FOREIGN_ID` mediumint(9) unsigned NOT NULL default '0', `FOREIGN_PARENT_ID` mediumint(9) NOT NULL default '0', `FOREIGN_TYPE` varchar(255) NOT NULL default '', `CREATED_BY` mediumint(9) NOT NULL default '0', `CREATED_DT` date NOT NULL default '-00-00', `MODIFIED_BY` mediumint(9) default NULL, `MODIFIED_TS` timestamp(14) NOT NULL, PRIMARY KEY (`APPROVAL_ID`), UNIQUE KEY `APPROVAL_APPLICATION` (`APPROVAL_APPLICATION`,`FOREIGN_ID`,`FOREIGN_TYPE`) ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; Is it the unique key I'm creating and if so how do I get around that? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok here is the code chunk: $rows = is_numeric($_GET['rows']) ? $_GET['rows'] : 5; $stRow = 0; // SEARCH CODE $sql = SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = '$state' limit $stRow, $rows; $search = mysql_query($sql); echo $sql; $sql = SELECT FOUND_ROWS(); $ctTotalResults = mysql_fetch_row(mysql_query($sql)); It errors out on the first $search = mysql_query(); statement. Matt Babineau Criticalcode w: http://www.criticalcode.com http://www.criticalcode.com/ p: 858.733.0160 e: [EMAIL PROTECTED] _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:25 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt Babineau [EMAIL PROTECTED] wrote on 02/01/2005 03:20:49 PM: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Mysqldump unusable, bugged?
On Tue, 1 Feb 2005, matt_lists wrote: now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX DIRECTORY='E:\\mysql\\data\\campbell\\' Anybody know? Is there some option I'm not setting which causes the data directory and index directory to not be correct? Why are there not hundreds of people with this problem, I would think it affects anyone on 4.x running on a windows machine, I hope they dont learn the hard way when a restore fails to work! Noone has found a way to reproduce it with 100% certainity. If you have a way to always reproduce, feel free to re-open my bug at: http://bugs.mysql.com/bug.php?id=6660 (Or send me the test-case and I can do it). I haven't gotten the bug approved since I can't reproduce it (but it does happen quite often, just haven't been able to find out why, not even by replaying binary logs). cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE problem
I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()which never happensno rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. ...Ren --- Ren Fournier www.renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested query bug
[snip] Not only that, shoudlnt it say column does not exist? It does not return an error, it ignores the whole nested query [/snip] Not really, because the entire sub query is being viewed as an OR condition (because of using IN). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld ended Can't get mysql running
Debra, look in /opt/mysql/data for an error file which should contain some hint of why it stopped.. could be many things.. permissions on the data files, full disk broken paths, etc... - michael dykman On Tue, 2005-02-01 at 15:43, Debora Gomes.unitech wrote: Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Dbora S. Gomes Analista de Tecnologia - Redes Agncia Nacional do Petrleo - ANP Tel.: 21 3804-0786 -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld ended Can't get mysql running
[EMAIL PROTECTED] wrote: We need more details in order to help: What operating system are you on? Which version of MySQL are you attempting to run? How are you attempting to start the MySQL server? Has it worked before or is this a new installation? What errors are being reported to your error log? The default location of the error logs is mysql installation folder/data/*.err I'm running into the same problem. (And what's really aggravating is that this happened to me 6 months ago and I didn't write down the solution! Jeesh!) - Installed 5.0 on FCore3. - Installed both the server and the client on the same box, via rpms. - The mysql and test databases were NOT created under /var/lib/mysql, though. - So I ran mysql_install_db as root. - ran '/usr/bin/mysqld_safe ' as root and got the same error as Deborah. - read the doc, and ran /usr/bin/mysqld_safe as an regular user and got a permission problem: [EMAIL PROTECTED] /tmp] Dude? Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 302: /var/lib/mysql/mybox.err: Permission denied /usr/bin/mysqld_safe: line 308: /var/lib/mysql/mybox.err: Permission denied STOPPING server from pid file /var/lib/mysql/mybox.com.pid tee: /var/lib/mysql/mybox.err: Permission denied 050201 14:47:40 mysqld ended tee: /var/lib/mysql/mybox.err: Permission denied [1]+ Exit 1 mysqld_safe --user=mysql - the permissions [EMAIL PROTECTED] /var/lib/mysql] I AM ROOT: ls -al total 40 drwxr-xr-x 4 mysql root 4096 Feb 1 13:45 . drwxr-xr-x 35 root root 4096 Feb 1 13:45 .. -rw-rw 1 mysql root 693 Feb 1 14:45 mybox.err drwx--x--x 2 mysql root 4096 Feb 1 13:45 mysql drwxr-xr-x 2 mysql root 4096 Feb 1 13:45 test [EMAIL PROTECTED] /var/lib/mysql] I AM ROOT: And I think those are OK. So, I can tell that: 1. mysql isn't running, and I can't get it to run 2. it isn't writing to the permissions file, but I don't know why Whil We would love to help but your message is just not specific enough. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Debora Gomes.unitech [EMAIL PROTECTED] wrote on 02/01/2005 05:43:02 PM: Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Res: Re: mysqld ended Can't get mysql running
Forwarded to the list - Shawn - Forwarded by Shawn Green/Unimin on 02/01/2005 03:49 PM - Debora Gomes.unitech [EMAIL PROTECTED] wrote on 02/01/2005 06:33:43 PM: The operating system is Red Hat 7.3 The MySQL version is 4.1.9 The error appears when i run ./configure in mysql/ directory. This is a new installation Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786 [EMAIL PROTECTED] Para: Debora Gomes.unitech [EMAIL PROTECTED] 01/02/2005 17:21 cc: mysql@lists.mysql.com Assunto: Re: mysqld ended Can't get mysql running ANP We need more details in order to help: What operating system are you on? Which version of MySQL are you attempting to run? How are you attempting to start the MySQL server? Has it worked before or is this a new installation? What errors are being reported to your error log? The default location of the error logs is mysql installation folder/data/*.err We would love to help but your message is just not specific enough. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Debora Gomes.unitech [EMAIL PROTECTED] wrote on 02/01/2005 05:43:02 PM: Hi ALL it reports back # Starting mysqld daemon with databases from /opt/mysql/data STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid 050201 02:43:51 mysqld ended Att. Débora S. Gomes Analista de Tecnologia - Redes Agência Nacional do Petróleo - ANP Tel.: 21 3804-0786
RE: DATE problem
From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql =SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
Tobias Asplund wrote: On Tue, 1 Feb 2005, matt_lists wrote: now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX DIRECTORY='E:\\mysql\\data\\campbell\\' Anybody know? Is there some option I'm not setting which causes the data directory and index directory to not be correct? Why are there not hundreds of people with this problem, I would think it affects anyone on 4.x running on a windows machine, I hope they dont learn the hard way when a restore fails to work! Noone has found a way to reproduce it with 100% certainity. If you have a way to always reproduce, feel free to re-open my bug at: http://bugs.mysql.com/bug.php?id=6660 (Or send me the test-case and I can do it). I haven't gotten the bug approved since I can't reproduce it (but it does happen quite often, just haven't been able to find out why, not even by replaying binary logs). cheers, Tobias I can repeat it every time, but only when I dump all tables, if I specify the bad tables that cause the problem, the extra parameters are not included I cant post my entire database due to security concerns if I dump only the affected table, the bug does not appear -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Matt Babineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:52 PM To: 'Michael Dykman' Cc: 'MySQL General' Subject: RE: Problem with SELECT SQL_CALC_FOUND_ROWS Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1071: Specified key was too long. Max key length is 500
Jon Wynacht [EMAIL PROTECTED] wrote on 02/01/2005 03:33:13 PM: Hi, I'm wondering if anybody can help me decrypt what this error code is referring to: ERROR 1071: Specified key was too long. Max key length is 500 This is the offending table creation script: CREATE TABLE `APPROVAL` ( `APPROVAL_ID` mediumint(9) unsigned NOT NULL auto_increment, `APPROVAL_CURRENT_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_HIGHEST_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_STATUS` varchar(255) NOT NULL default '', `APPROVAL_SUB_STATUS` varchar(255) default NULL, `APPROVAL_APPLICATION` varchar(255) NOT NULL default '', `FOREIGN_ID` mediumint(9) unsigned NOT NULL default '0', `FOREIGN_PARENT_ID` mediumint(9) NOT NULL default '0', `FOREIGN_TYPE` varchar(255) NOT NULL default '', `CREATED_BY` mediumint(9) NOT NULL default '0', `CREATED_DT` date NOT NULL default '-00-00', `MODIFIED_BY` mediumint(9) default NULL, `MODIFIED_TS` timestamp(14) NOT NULL, PRIMARY KEY (`APPROVAL_ID`), UNIQUE KEY `APPROVAL_APPLICATION` (`APPROVAL_APPLICATION`,`FOREIGN_ID`,`FOREIGN_TYPE`) ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; Is it the unique key I'm creating and if so how do I get around that? Thanks, Jon You are correct. Your UNIQUE key definition is failing because it cannot be composed of more than 500 characters. If I add up the display sizes of the columns you want to use to build your UNIQUE index from I get 519 bytes(255+9+255 = 519). May I suggest that you do not use varchar(255) for those fields? Using varchar(200) (or something MUCH smaller, perhaps) should suffice to store the actual data values you need in those fields. Then you could create your UNIQUE index without going over the 500 character limit. The alternative is to only index a portion of your varchar(255) fields. You do that by putting the length of the field after the field name enclosed by parentheses. ( http://dev.mysql.com/doc/mysql/en/create-index.html) You would do something like this: UNIQUE (`APPROVAL_APPLICATION` (200), `FOREIGN_ID`, `FOREIGN_TYPE` (200)) That still creates an index with up to 409 characters in it. This is a rather WIDE index entry and will slow you down when it comes time to search it. I highly recommend reducing the sizes of the fields to what you will need to store the actual data. IF you don't know (because you are importing some unknown data) then save the creation of the UNIQUE index until after you have read in all of the data. That way you will know the true sizes of the data for those fields. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help please : ERROR 2006: MySQL server has gone away
Hi, I have this error when I am connected in mysql: ERROR 2006: MySQL server has gone away. No connection. Trying to reconnect... I only did this command: show databases; and received the error. I noticed that if I don't send a command since 25 sec, I have the error. Also, my variable wait_timeout = 3600. Any ideas ? David Marois mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
RE: Nested query bug
[snip] No, it isn't ignored...it just returns a FALSE for the IN statement [/snip] More info The word IN is an alias for = ANY. Thus these two statements are the same: SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2); However, NOT IN is not an alias for ANY, but for ALL. See Section 13.1.8.4, Subqueries with ALL. From http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE problem
Mike Johnson wrote: From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! It will work, but you don't want to do that. As soon as you run your column through a function, you can no longer use the index on that column to choose rows. In other words, this query results in a full table scan. Mysql will have to execute DATE_FORMAT() on table.created for every single row to make the comparison. Instead, you should always compare columns to constants, if possible. That's not as bad as it may sound, since functions of constants are constants. In this case (assuming no rows with created in the future), you should use SELECT COUNT(table.id) FROM table WHERE table.created = CURDATE(); Mysql will calculate CURDATE() once, convert it to a DATETIME by adding zeros, then select matching rows using the index on table.created. Another example: Say you wanted the rows which were created in the last 72 hours. Here are some equivalent conditions: WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(created) = 72*60*60 WHERE created + INTERVAL 72 HOUR = NOW() WHERE created = NOW() - INTERVAL 72 HOUR Only the last one can use an index on created, however. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SELECT SQL_CALC_FOUND_ROWS
I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still unresolved? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ut8 turkish character problem
What does [Show VARIABLES LIKE "%char%" ;] return ? (B (BHow does the character look when selected on the command line ? (B -- If correct then inserting works fine (B -- If not corect check your character settings (B (BYou might also find the information below useful: (B (Bhttp://www.be-known-online.com/mysql/ (B(Using_MySQL_in_a_Japanese_environment (380KB PDF)) (Bhttp://dev.mysql.com/doc/mysql/en/charset.html (B (B (BBest regards (B (BNils Valentin (BTokyo / Japan (B (B (B (B (B I have mysql-4.1.9 installed. I am trying to insert and select turkish (B characters. (B My tables are utf8. I tried everything but not succesfull. turkish (B characters (B returns to "?" or crops on turkish character. "example: insert: Bing?, (B select: Bing" (B (B What is the secret of inserting turkish characters. (B (B I tried things like; (B SET NAMES 'utf8'; (B SET CHARACTER SET 'utf8'; (B But nothing solved problem. (B (B If anoybody knows how to insert and select turkish characters please help. (B (B PS. I tried using myODBC 2.5, 3.51.* all 3.51 versions but i got same (B result on all versions of myodbc. (B Also I tried with ASP and ASP.NET (B (B (B (B -- (B MySQL General Mailing List (B For list archives: http://lists.mysql.com/mysql (B To unsubscribe: (B http://lists.mysql.com/[EMAIL PROTECTED] (B (B (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
I do see that the bug was closed over 2 years ago - I guess then it was a persistent problem, but now it is limited to the configuration of the MySQL Server. As I have found out today, Quadrupling the stock memory limits on the MySQL Server solved the problem...not sure what will happen when the database starts growing but, my query is solid, and I am only returning a few rows at a time, so hopefully over a couple hundred queries wont make this thing die! :) Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:55 PM To: Michael Dykman Cc: Matt Babineau; 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still unresolved? Michael Michael Dykman wrote: Matt, If you go through the various build options tried in that bug report, you will see that it is a subtle bug and apparently still unresolved. I couldn't tell you with any degree of confidence will fix it. The bug report does show a couple of configure options for PHP which seem to elimiate it, without shedding much light on the underlaying cause. Possible conflict in zlib versions or some other common library between PHP and MySQL build.. I would closely examine the dependant libraries via $ ldd... Hard to tell without knowing the OS, the specific software builds and the various library versions especially any shared ones.. If you have a test system you can use, you might consider experimentally building both PHP and MySQL from source and explicitly set them to use the same versions of any shared libraries? Might be a bit of overkill... - michael dykman On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: Warning mysql_query(): Unable to save result set in /clients/search.php My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1071: Specified key was too long. Max key length is 500
Shawn, I tried both of those suggestions and they work well, with reducing the size of the fields being the way I'm going to go. Thanks for your help! Cheers, Jon On Feb 1, 2005, at 1:12 PM, [EMAIL PROTECTED] wrote: Jon Wynacht [EMAIL PROTECTED] wrote on 02/01/2005 03:33:13 PM: Hi, I'm wondering if anybody can help me decrypt what this error code is referring to: ERROR 1071: Specified key was too long. Max key length is 500 This is the offending table creation script: CREATE TABLE `APPROVAL` ( `APPROVAL_ID` mediumint(9) unsigned NOT NULL auto_increment, `APPROVAL_CURRENT_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_HIGHEST_LEVEL` mediumint(8) unsigned NOT NULL default '1', `APPROVAL_STATUS` varchar(255) NOT NULL default '', `APPROVAL_SUB_STATUS` varchar(255) default NULL, `APPROVAL_APPLICATION` varchar(255) NOT NULL default '', `FOREIGN_ID` mediumint(9) unsigned NOT NULL default '0', `FOREIGN_PARENT_ID` mediumint(9) NOT NULL default '0', `FOREIGN_TYPE` varchar(255) NOT NULL default '', `CREATED_BY` mediumint(9) NOT NULL default '0', `CREATED_DT` date NOT NULL default '-00-00', `MODIFIED_BY` mediumint(9) default NULL, `MODIFIED_TS` timestamp(14) NOT NULL, PRIMARY KEY (`APPROVAL_ID`), UNIQUE KEY `APPROVAL_APPLICATION` (`APPROVAL_APPLICATION`,`FOREIGN_ID`,`FOREIGN_TYPE`) ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; Is it the unique key I'm creating and if so how do I get around that? Thanks, Jon You are correct. Your UNIQUE key definition is failing because it cannot be composed of more than 500 characters. If I add up the display sizes of the columns you want to use to build your UNIQUE index from I get 519 bytes(255+9+255 = 519). May I suggest that you do not use varchar(255) for those fields? Using varchar(200) (or something MUCH smaller, perhaps) should suffice to store the actual data values you need in those fields. Then you could create your UNIQUE index without going over the 500 character limit. The alternative is to only index a portion of your varchar(255) fields. You do that by putting the length of the field after the field name enclosed by parentheses. (http://dev.mysql.com/doc/mysql/en/create-index.html) You would do something like this: UNIQUE (`APPROVAL_APPLICATION` (200), `FOREIGN_ID`, `FOREIGN_TYPE` (200)) That still creates an index with up to 409 characters in it. This is a rather WIDE index entry and will slow you down when it comes time to search it. I highly recommend reducing the sizes of the fields to what you will need to store the actual data. IF you don't know (because you are importing some unknown data) then save the creation of the UNIQUE index until after you have read in all of the data. That way you will know the true sizes of the data for those fields. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to store numeric data?
I've got a huge table going, and it's storing a load of numeric data. Basically, a percentage or single digit rank, one or two digits before the decimal and fifteen after, like this: 6.984789027653891 39.484789039053891 What is the most efficient way to store these values? I will be frequently sorting results by them or using math with them, so speed is important, but I also don't want to be wasteful of disk space as I currently have over three quarters of a million records, with more to come. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
