Converting char data ISO8601 to Unixtime Error
Afternoon all,
This is an interesting problem that we ran into and have subsequently fixed (well it
fixed
itself, but more on that later).
I posted a question to the list a couple days ago quoting this as a TIMESTAMP(14)
column type
issue, but as I investigated more, that turned out to be incorrect.
About 3 days ago, four of our tables started reporting incorrect date/times, they were
all out by 1 day in the future.
These errors were localised to the fields with a column type of TIMESTAMP(14), our
only TIMESTAMP(14) columns
in our database (save for a few in tables where they are used as a date/time marker,
i.e., using the INSERT with NULL
to set it to now).
As it turns out the issue seemed to be this:
MySQL added 1 day (24 hours) when converting from character data MMDDHHMMSS to
Unixtime
(seconds since epoch).
The effects were thus:
* Doing an explicit INSERT INTO Table SET TimeStampField = '20031210235959'
Would put '20031211235959' into the table.
* Doing a SELECT UNIX_TIMESTAMP('20031210233150') would return the unixtime of
20031211233150,
In the example below, our time offset is UTC + 13hrs. The unixtime for
'20031210233150' should be 1071052310:
mysql SELECT UNIX_TIMESTAMP('2003-12-10 23:31:50');
+---+
| UNIX_TIMESTAMP('2003-12-10 23:31:50') |
+---+
|1071138710 |
+---+
1 row in set (0.23 sec)
mysql SELECT FROM_UNIXTIME( 1071138710 );
+-+
| FROM_UNIXTIME( 1071138710 ) |
+-+
| 2003-12-11 23:31:50 |
+-+
1 row in set (0.20 sec)
* TIMESTAMP(14) fields where we never explicitly set the value, were
unaffected.
* There is no issue with mktime() at an OS level as alternative conversions
using Perl were correct.
* We are running v3.23.49a on Red Hat Linux v7.2
-
While investigating this issue, the problem corrected itself!! We did nothing. Somehow
43 hours after the
problem arose, it suddenly went away, and we've spent the time since massaging the
affected records back to
normal.
Certainly a very strange problem and very disconcerting since it corrected itself
without any intervention on our
part, it's making us ask if it has happened before and will it happen again.
Just putting this one out there :)
Matthew M. Boulter
Software Engineer
Saab ITS Pty Ltd
Mobile: +61 (0)415 169 088
Phone: +61 (0)7 3854-4815
Fax:+61 (0)7 3854-4899
Email: [EMAIL PROTECTED]
WWW:www.saab-its.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select with inner join NOT IN / IN
Hello I need help This Statement does not work. MySql Version is 3.23.47. On Oracle Instance it works. What can i do ? Whitch Version do i need ? MySql 4.xx ? MySQL 3.23.47 running on localhost SELECT A.Kommentar, A.SequenzNr from Aufgabe A LEFT JOIN Taetigkeiten ON Aufgabe.Kommentar = Taetigkeiten.Bezeichnung where inaktiv = '1' ) ; Error SQL-query : SELECT A.Kommentar, A.SequenzNr FROM Aufgabe A WHERE A.kommentar NOT IN ( SELECT Bezeichnung FROM Taetigkeiten where inaktiv = '1') LIMIT 0, 30 MySQL said: You have an error in your SQL syntax near 'SELECT Bezeichnung FROM Taetigkeiten where inaktiv = '1' ) LIMIT 0, 30' at line 1 Thanks Jens Gronau - Otto (GmbH Co KG) Wandsbeker Straße 3-7 22172 Hamburg Jens Gronau Abt. IT-GA-DR Fon: +49 40 64617473 Fax: +49 40 64647473 mailto: Jens:[EMAIL PROTECTED] - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with inner join NOT IN / IN
Hi Jens, try to rewrite you query to SELECT A.Kommentar, A.SequenzNr FROM Aufgabe A LEFT JOIN Taetigkeiten ON A.kommentar=Taetigkeiten.Bezeichnung AND Taetigkeiten.inaktiv = '1' WHERE isnull(Taetigkeiten.Bezeichnung) LIMIT 0, 30 -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com - Original Message - From: Gronau, Jens To: [EMAIL PROTECTED] Sent: Friday, December 12, 2003 2:04 PM Subject: Select with inner join NOT IN / IN Hello I need help This Statement does not work. MySql Version is 3.23.47. On Oracle Instance it works. What can i do ? Whitch Version do i need ? MySql 4.xx ? MySQL 3.23.47 running on localhost SELECT A.Kommentar, A.SequenzNr from Aufgabe A LEFT JOIN Taetigkeiten ON Aufgabe.Kommentar = Taetigkeiten.Bezeichnung where inaktiv = '1' ) ; Error SQL-query : SELECT A.Kommentar, A.SequenzNr FROM Aufgabe A WHERE A.kommentar NOT IN ( SELECT Bezeichnung FROM Taetigkeiten where inaktiv = '1') LIMIT 0, 30 MySQL said: You have an error in your SQL syntax near 'SELECT Bezeichnung FROM Taetigkeiten where inaktiv = '1' ) LIMIT 0, 30' at line 1 Thanks Jens Gronau
Re: --quote-names doesn't work for databasename
Hi! On Dec 11, Gerald wrote: The subject says most of it. I'm moving mysql data from one machine to another. --quote-names works on tables and columns, but it seems to be skipping the database names. (`-` is the character throwing the restore off) Check the bugdb (bugs.mysql.com) as far as I rememebr this bug was already reported there and was fixed. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK I tried this, so '+music +mix +2001' instead of this 'music mix
2001' and the SQL time is the same ~21 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('+music +mix +2001' IN BOOLEAN MODE)
and artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 21:50
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
It makes sense, but Sergei G. said :
And are you sure the numbers are correct, the first query - the one
without IN BOOLEAN MODE - is faster ? I would expect the opposite.
I guess that for my DB I can't expect satisfied in boolena mode
times
?
But also when searching without in boolean mode and include search
criteria from TRACKS table, 13,841,930 rows , like AND MATCH (
tracks.title) AGAINST ('remix')
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the special characters, like
+-*. I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('+music +mix +2001'IN BOOLEAN MODE)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
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: Login Problems
After I uninstalled 4.1 I removed the /var/db/mysql/ directory so that the install of 4.0.6 would start fresh and create its defualt users and databases with no trace of the previous user entries. Schrodinger wrote: I have removed all localhost user entries and all users should now be able to connect from any host. But when a user attempts to connect with a password they get the usual ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) In an earlier message, you said you downgraded from mysql 4.1.? to 4.0.6 (Why not 4.0.16?). mysql 4.1 has a different password format in the user table than previous versions. Pre 4.1.x mysql uses 16 byte hashes to store passwords, 4.1.0 uses 45 byte hashes, and 4.1.1 (and up) uses 41 byte hashes. If you kept (or restored) the mysql user table you created in 4.1, you may now be comparing a 16 byte password hash to a 41 or 45 byte hash. As I understand it, the only way that could match is if both are blank. If this is the case for you, use GRANT to reset the passwords to new 16 byte hashes. See http://www.mysql.com/doc/en/Password_hashing.html for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
[snip] After I uninstalled 4.1 I removed the /var/db/mysql/ directory so that the install of 4.0.6 would start fresh and create its defualt users and databases with no trace of the previous user entries. [/snip] Are you doing your grants from the command line or from a GUI? Have you done FLUSH PRIVILEGES? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
I created users using webmin and I have done FLUSH PRIVILEGES. [snip] After I uninstalled 4.1 I removed the /var/db/mysql/ directory so that the install of 4.0.6 would start fresh and create its defualt users and databases with no trace of the previous user entries. [/snip] Are you doing your grants from the command line or from a GUI? Have you done FLUSH PRIVILEGES? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
[snip] I created users using webmin and I have done FLUSH PRIVILEGES. [/snip] Are you granting privileges based on 'localhost', '127.0.0.1', or somewhere else. Where are users logging in from? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF on AMD64
Well that was easy. After reading your response I went back and used gcc -fPIC -c xxx.cc gcc -shared -o xxx.so xxx.o and all was well again. Thanks for your help..again. Ollie Dan Nelson said: In the last episode (Dec 11), Ollie Gallardo said: I'm back with another question. I tried to compile my UDF with the gcc on my Opteron system and I got errors. Errors: /usr/lib64/gcc-lib/amd64-mandrake-linux- gnu/3.3.1/../../../../lib64/crt1.o(.text+0x21): In function `_start': ../sysdeps/x86_64/elf/start.S:92: undefined reference to `main' What is your gcc line? The above error indicates that you are trying to build an executable. UDFs must be shared object files. http://www.mysql.com/doc/en/UDF_compiling.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Ollie Gallardo Support Services Inc 2 Professional Dr Ste 212 Gaithersburg MD 20879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
The users are being created with the privelages of being able to login from any host and they are logging in from the command line through their shell account. [snip] I created users using webmin and I have done FLUSH PRIVILEGES. [/snip] Are you granting privileges based on 'localhost', '127.0.0.1', or somewhere else. Where are users logging in from? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
[snip] The users are being created with the privelages of being able to login from any host and they are logging in from the command line through their shell account. [/snip] So they need to be '[EMAIL PROTECTED]' You gave this ... [snip] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:YES) [/snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile problem
[EMAIL PROTECTED] wrote: I'm load a CSV file with five fields into mysql. It loads up fine. Problem comes in when I try to refresh the data with updates. I grab this file and convert it to CSV every 12 hours. The data shows past 24 hours only, so basically I'm trying to make a permanent archive. I tried creating a PRIMARY key of the first 4 fields to be unique to filter out duplicates. Problem is, when I have that key on, no new data gets written to the database, even clearly NEW records. Table is warlog, fields are time, attacker, coords, defender, status (obviously a game). A unique record would match the first four, or at the very least time and coords. Whenever I go to load data infile the second time around, no records get written if any indexes are present. If no indexes I get duplicates. If I put an index with the duplicates, I only get the first set of data with no updates. Is there something about primary keys I should know about? I've created this database with them and tried every combination, but I can't seem to get the update part to work. If you don't specify IGNORE or REPLACE keyword, LOAD DATA LOCAL works the same as IGNORE is specified. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
Issue has been resolved. Thank you all. I dont know what went wrong but I think it was becuase when I was connecting as root I ran : mysql --user=root and not mysql --user=root mysql after I did this I created a user and now that user can connect with a password. Thank you all for your help and now a computer society's web site has been born. Regards, Conor. [snip] The users are being created with the privelages of being able to login from any host and they are logging in from the command line through their shell account. [/snip] So they need to be '[EMAIL PROTECTED]' You gave this ... [snip] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:YES) [/snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
I am using the command line for grant operations, no flushing though Why are you using 4.0,6? any special reason? I am using a custom (i compiled myself) 4.0.16 Here is an example operation: TRY TO LOG WITHOUT PASS, ERROR [EMAIL PROTECTED] vmedina]$ mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) *** TRY TO LOG WITH PASSWORD, SUCCESS! [EMAIL PROTECTED] vmedina]$ mysql -u vmedina -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.16-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. ** CREATE A USER, NO FLUSH, LOGOUT mysql grant all privileges on *.* to test@'%' identified by '1234' with grant option; Query OK, 0 rows affected (0.00 sec) mysql exit Bye LOG INTO MYSQL USING THE NEW USER, PROVIDE PASS, SUCCESS [EMAIL PROTECTED] vmedina]$ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.16-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql *** USE THE NEW USER TO CREATE *** A YET AGAIN A NEW USER, NO FLUSH [EMAIL PROTECTED] vmedina]$ mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.16-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql grant all privileges on *.* to test03@% identified by '1234' with grant option; Query OK, 0 rows affected (0.00 sec) mysql I've never used flush. I must say that the only user in that machine is a user called vmedina, that's me, 'cause it is my development workstation, as i told you i deleted ALL users after i created the vmedina user, simply because there is no need for others, vmedina user can handle all admin operations in a MySQL server, and create other users as needed. If you need anything else just let me know my friend :) Best Regards -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Local Infile problem
This is from the manual. See the 3rd paragraph. The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values. If you specify REPLACE, input rows replace existing rows (in other words rows that has the same value for a primary or unique index as an existing row). See section 6.4.7 REPLACE Syntax. If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, the behavior depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SORTing / LIMITing on max(fieldname) blah-blah
. I'm seeing double with this, and I just KNOW it's got to be simple - The table images contains columns: id | reference_number | image | width | height That's all it contains, nothing else. id is auto-increment reference_number is usually set anew by the operator to input new data, but sometimes he returns to an earlier one to change data. Each new reference_number will not always be higher than the previous one. The reference_number being worked will not always be the highest in its sequence. There are always eight images per reference_number, numbered 01 to 08, with their different widths and heights. All types are integer. As the operator inputs new data for the reference_number he's just newly set, or returned to, I want his monitor to display, on-the-run, all the data he's input for the reference_number he's working (even if the reference_number is newly set, he's just input image 01 and there are no images 02 to 08 yet) and order it by increasing image number. Things like: SELECT id, image, width, height, max(id) AS top FROM images WHERE reference_number=top ORDER BY image one of a seeming hundred variants that I've tried, plus others with LIMIT and HAVING, just give MySQL indigestion. (Perhaps I'm over-egging the pudding with the description, but it's helping me make sure I have it right.) I really need a holiday... Any thoughts? (No, not where I should go for the holiday!) T.I.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql died a hard death after using grant and won't restart
perror 145 145 = Table was marked as crashed and should be repaired. Run myisamchk on it. David Rankin wrote: I can't figure this out. I'm setting privileges for access on a local net to a user [EMAIL PROTECTED] using grant and all of a sudden mysql is dead. I'm running 3.23.31 on Mandrake 7.2. I haven't had any problems in years. Anybody got any thoughts on this? Please reply to [EMAIL PROTECTED] What in the heck could cause a Bogus stack limit or frame pointer, aborting backtrace ?? The applicable part of the .err log is: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 031211 20:38:21 mysqld restarted 031211 20:38:21 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld: ready for connections 031211 21:40:40 /usr/sbin/mysqld: Normal shutdown 031211 21:40:40 /usr/sbin/mysqld: Shutdown Complete 031211 21:40:40 mysqld ended 031211 21:40:47 mysqld started 031211 21:40:47 /usr/sbin/mysqld: Can't open file: 'user.MYD'. (errno: 145) 031211 21:40:47 mysqld ended next I mv /datadir/mysql /datadir/mysql-old then do a mysql_install_db and try again. Same error results mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Cannot determine thread, ebp=0xb, backtrace may not be correct Bogus stack limit or frame pointer, aborting backtrace -- David C. Rankin, J.D., P.E. Rankin * Bertin, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field Name whitespace via MyODBC
Yes, if you are using v3.23.6 or above, ALTER TABLE on the command line should accept quoted column names to allow you to change the column names. Pat... - Original Message - From: Brian Duke [EMAIL PROTECTED] To: 'Patrick Sherrill' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 11:19 AM Subject: RE: Field Name whitespace via MyODBC I can just alter table to rename the fields right? -Original Message- From: Patrick Sherrill [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 5:27 AM To: Brian Duke; [EMAIL PROTECTED] Subject: Re: Field Name whitespace via MyODBC Brian, I think you are going to need to rename your fields/columns. If you are unable to rename them in mysql then you will probably need to rename them in your jet database with Access and re-import them. Your column names should be literals not wrapped in graves or quotes and should contain no whitespace. Whitespace is frequently used as a delimiter. I also avoid any characters other than alphanumeric and the occasional underscore character in field/column names . It helps avoid OS idiosyncrasies and simplifies naming conventions. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: Brian Duke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 2:50 AM Subject: Field Name whitespace via MyODBC On the windows machine I have Acess2003. I installed the MyODBC to push the data into the FreeBSD MySQL server. The connection worked like a dream. Kudos to the MyODBC team. The table that the connection created included field names that have spaces in the name. I'm trying to access the data thru my PHP scripts. I tried this: $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , `LERG_7_SHA.SHA INDICATOR` , `LERG_7_SHA.H ORG B TDM` FROM LERG_7_SHA WHERE ( LERG_7_SHA.SWITCH = \$npa\ AND `LERG_7_SHA.SHA INDICATOR` = \$nxx\ ) LIMIT 0, 30; And $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , \'LERG_7_SHA.SHA INDICATOR\' , \'LERG_7_SHA.H ORG B TDM\' ,\'LERG_7_SHA.H ORG C TDM\' , LERG_7_SHA.HOST , LERG_7_SHA.OCN , LERG_7_SHA.AOCN FROM LERG_7_SHA WHERE (LERG_7_SHA.SWITCH = \$npa\ AND \'LERG_7_SHA.SHA_INDICATOR\' = \$nxx\ ) LIMIT 0, 30; Both do not work. The query breaks down where the backticks are or tries to add the literal string of 'LERG_7_SHA.SHA INDICATOR' in the result data. I have tried with single quotes and double quotes. I can issue this command on the mysql command line and it does work. Can someone help me syntax this line correct? The script.php and the database are on the same FreeBSD machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple languages in the same column
We are using MySQL 4.1.1 with mysql-connector-java-3.1 JDBC Driver(nightly snapshot). The only way we can store and display the Unicode content is by specifying it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 AND specifying the table/column type as CHARACTER SET utf8. e.g.: create table test_table (col1 VARCHAR(10) CHARACTER SET utf8) None of the followings we tried work, if we do not specify it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 1.We tried to set database's default character set to UTF-8 like: mysqlalter database database_name default character set utf8; 2.We tried to specify the table/column type as CHARACTER SET utf8 3.We tried to set the default character set to utf-8 in the my.ini by adding the following line: default-character-set=utf8 Is my understanding correct? Thanks -Yayati -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 11:43 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Multiple languages in the same column -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Puny Sen wrote: Hi All, I'd like to use the same column to store content from multiple languages (English, German, French, Japanese). Here is my understanding of the options available. In MySQL 4.0: - UTF-8 is not currently available as a charset True. - we can connect to the database using useUnicode=truecharacterEncoding=UTF-8 in the connection string. True. - this enables us to store, search and retrieve Unicode content from the column, as long as we always use JDBC with the above connection string, to interact with the db. True. - sorting will not work on the column True. In MySQL 4.1: - UTF-8 is available as a charset Yes, but remember, UTF-8 is an _encoding_ that can store many different character sets, there is a difference. - We still neet to connect to the database using the above connection string (doesn't seem to work otherwise) Unless you set your database's default character set to UTF-8, then yes, you do still need to have 'useUnicode=truecharacterEncoding=UTF-8' in your URL, which tells the driver that you will be mixing character sets in your queries (so encode them as UTF-8), and also tells the server to expect your queries to be encoded in UTF-8 (the driver does a 'SET NAMES UTF-8' on connect in this case). - sorting will work, but only using the general utf8 collation (may not work for Japanese?). More collations will be available soon. True. If you know the column charset and collation that you want to use, you should be able to use CAST on it to get it to a different charset, and the sort using a compatible collation. - [can we cast/convert to a different charset (sjis) and use its collation for sorting? (performance is not really an issue)] I guess I just answered that above :) Please let me know if any of these assumptions are incorrect. They seem to be correct. Please let me know if you run into any issues or inconsistencies with these assumptions, because the combination of Unicode and UTF-8 support in the JDBC driver and the server is new (and can in sometimes be complex, due to the flexibility it offers), and we'd like to get any kinks worked out ASAP! -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/2J6ItvXNTca6JD8RAp3BAJ9sWug9JcCeqWrDGzg6XGc2bUTaWwCgxcap SRKikpcyoo0St5ClUF9G4Dw= =QaD8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to keep multiple instances of the same information from being displayed
Using PHP as the front end I am creating a form with a dropdown box that
displays information from one table to be inserted into another table.
That's PHP and I have gotten that part down with no issue.
However, in the table a person could be listed multiple times and I only
want a user to be listed a single time. for instance in the table I may have
Jones, Jim
Jones, Mary
Jones, Mary
Jones, Mary
Jones, Nancy
Jones, Paul
when I do my SELECT I only want Mary Jones to show up a single time.
Here is the code I am currently using within the Dropdown box:
SNIPPET
$Link = mysql_connect($Host, $User, $Password);
$Query=SELECT * from $TableName ORDER BY userlastname;
$Result= mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array ($Result)){
print (option value='$Row[userlastname],
$Row[userfirstname]'$Row[userlastname], $Row[userfirstname]/option);
}
mysql_close ($Link);
?
/SNIPPET
As you can see I am pulling two fields from one table, then combining them
to make a single field in another table. I am doing this to provide
continuity when I create queries in the future.
Any help with this would be greatly appreciated
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making hotbackup
I am running a mysql database and want to perform a hotbackup on linux. How will this be done since the ibbackup tool is not delivered in the standard package
RE: How to keep multiple instances of the same information from b eing displayed
Jeff,
The first example worked perfectly, Thanks for the quick response
Jess
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]
Sent: Friday, December 12, 2003 10:02 AM
To: Hunter, Jess
Subject: Re: How to keep multiple instances of the same information
from being displayed
Using DISTINCT will solve your problem
SELECT DISTINCT userlastname, userfirstname FROM TABLE ORDER BY
userlastname
You could get this all out at once also by using CONCAT_WS
**untested sql**
SELECT DISTINCT CONCAT_WS(', ', userlastname, userfirstname) FROM TABLE
ORDER BY userlastname
HTH
Jeff
Hunter, Jess
[EMAIL PROTECTED]To:
[EMAIL PROTECTED]
RC.ORG cc:
Subject: How to keep
multiple instances of the same information from being
12/12/2003 10:47 displayed
AM
Using PHP as the front end I am creating a form with a dropdown box that
displays information from one table to be inserted into another table.
That's PHP and I have gotten that part down with no issue.
However, in the table a person could be listed multiple times and I only
want a user to be listed a single time. for instance in the table I may
have
Jones, Jim
Jones, Mary
Jones, Mary
Jones, Mary
Jones, Nancy
Jones, Paul
when I do my SELECT I only want Mary Jones to show up a single time.
Here is the code I am currently using within the Dropdown box:
SNIPPET
$Link = mysql_connect($Host, $User, $Password);
$Query=SELECT * from $TableName ORDER BY userlastname;
$Result= mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array ($Result)){
print (option value='$Row[userlastname],
$Row[userfirstname]'$Row[userlastname], $Row[userfirstname]/option);
}
mysql_close ($Link);
?
/SNIPPET
As you can see I am pulling two fields from one table, then combining them
to make a single field in another table. I am doing this to provide
continuity when I create queries in the future.
Any help with this would be greatly appreciated
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SORTing / LIMITing on max(fieldname) blah-blahT
fatblokeonbike wrote: . I'm seeing double with this, and I just KNOW it's got to be simple - The table images contains columns: id | reference_number | image | width | height That's all it contains, nothing else. id is auto-increment reference_number is usually set anew by the operator to input new data, but sometimes he returns to an earlier one to change data. Each new reference_number will not always be higher than the previous one. The reference_number being worked will not always be the highest in its sequence. There are always eight images per reference_number, numbered 01 to 08, with their different widths and heights. All types are integer. As the operator inputs new data for the reference_number he's just newly set, or returned to, I want his monitor to display, on-the-run, all the data he's input for the reference_number he's working (even if the reference_number is newly set, he's just input image 01 and there are no images 02 to 08 yet) and order it by increasing image number. Things like: SELECT id, image, width, height, max(id) AS top FROM images WHERE reference_number=top ORDER BY image one of a seeming hundred variants that I've tried, plus others with LIMIT and HAVING, just give MySQL indigestion. (Perhaps I'm over-egging the pudding with the description, but it's helping me make sure I have it right.) I really need a holiday... Any thoughts? (No, not where I should go for the holiday!) T.I.A. If I understand you correctly, you need the id of the last insert/update so you can pull all the rows with the same reference_number. You can get the last inserted/updated id with the LAST_INSERT_ID function, use that to recover the reference_number, then get the desired rows. Try this: SELECT @ref:=reference_number FROM images WHERE id=LAST_INSERT_ID(); SELECT * FROM images WHERE [EMAIL PROTECTED] ORDER BY image; Or, you could do it in a single select with a join: SELECT im1.id, im1.image, im1.width, im1.height FROM images AS im1, images AS im2 WHERE im1.reference_number = im2.reference_number AND im2.id = LAST_INSERT_ID() ORDER BY i1.image; See http://www.mysql.com/doc/en/Miscellaneous_functions.html for more on LAST_INSERT_ID(). Hope that helps. If that's not what you meant, give an example showing how the output should look to help us see what you want. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [RE-REPOST] Openssl support not activated?
OK. I've made some progress. It looks like just doing --with-openssl
doesn't work. You have to specify both the includes and the libs as
well. So, this is what I'm using now:
./configure --with-openssl
--with-openssl-includes=/usr/local/ssl/include
--with-openssl-libs=/usr/local/ssl/lib --with-vio
--prefix=/usr/local/mysql4016
I do get #define HAVE_OPENSSL 1 in my config.h and config.log.
However, it doesn't build. Here's what I get:
make[2]: Entering directory `/usr/local/src/mysql-4.0.16/strings'
source='strxmov.c' object='strxmov.o' libtool=no \
depfile='.deps/strxmov.Po' tmpdepfile='.deps/strxmov.TPo' \
depmode=gcc /bin/sh ../depcomp \
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include-O3
-DDBUG_OFF -c `test -f strxmov.c || echo './'`strxmov.c
In file included from /usr/include/linux/config.h:4,
from /usr/include/asm/atomic.h:4,
from ../include/my_global.h:273,
from strxmov.c:33:
/usr/include/linux/autoconf.h:64: warning: `CONFIG_SMP' redefined
../include/my_global.h:271: warning: this is the location of the
previous definition
In file included from strxmov.c:33:
../include/my_global.h:1109: openssl/opensslv.h: No such file or directory
make[2]: *** [strxmov.o] Error 1
make[2]: Leaving directory `/usr/local/src/mysql-4.0.16/strings'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/local/src/mysql-4.0.16'
make: *** [all] Error 2
It's fairly clear what's happening here. The openssl include directory
isn't being added into the list of -I's. I fixed it locally by adding
${openssl_includes} to the INCLUDES line in the makefile. However, I
had to do the same thing in the dbug, mysys, extra, regex, isam, merge,
innobase/*... I gave up after a few of these.
Any ideas where to go from here?
This is for mysql 4.0.16, openssl 0.9.7c and gcc 2.95.4 on a Debian
linux box (2.2.20).
Thanks.
-Greg G
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to keep multiple instances of the same information from being displayed
Perhas you want SELECT DISTINCT ?
http://www.mysql.com/doc/en/SELECT.html
Hope that helps,
Ken
- Original Message -
From: Hunter, Jess [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 10:47 AM
Subject: How to keep multiple instances of the same information from
being displayed
Using PHP as the front end I am creating a form with a dropdown box
that
displays information from one table to be inserted into another
table.
That's PHP and I have gotten that part down with no issue.
However, in the table a person could be listed multiple times and I
only
want a user to be listed a single time. for instance in the table I
may have
Jones, Jim
Jones, Mary
Jones, Mary
Jones, Mary
Jones, Nancy
Jones, Paul
when I do my SELECT I only want Mary Jones to show up a single time.
Here is the code I am currently using within the Dropdown box:
SNIPPET
$Link = mysql_connect($Host, $User, $Password);
$Query=SELECT * from $TableName ORDER BY userlastname;
$Result= mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array ($Result)){
print (option value='$Row[userlastname],
$Row[userfirstname]'$Row[userlastname],
$Row[userfirstname]/option);
}
mysql_close ($Link);
?
/SNIPPET
As you can see I am pulling two fields from one table, then
combining them
to make a single field in another table. I am doing this to provide
continuity when I create queries in the future.
Any help with this would be greatly appreciated
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
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: SORTing / LIMITing on max(fieldname) blah-blah
One thing that I see is that you are using an alias in your where clause. From the manual: It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias. The other thing that I see is that Max() is an aggregate function and that you do not have a group by clause. I would think that this query wouldn't run at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SORTing / LIMITing on max(fieldname) blah-blah
Or you could use a subquery: SELECT id, image, width, height FROM images WHERE reference_number=(Select max(id) From images) ORDER BY image -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables rights
Hi, I'm granting users to use temporary tables as: GRANT CREATE TEMPORARY TABLES ON db1.* TO user1; and having grants on many other tables as: GRANT SELECT,INSERT, UPDATE, DELETEON db1.table1 TO user1; GRANT SELECT,INSERT, UPDATE, DELETEON db1.table2 TO user1; but how make this work CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1; ok SELECT * FROM tmp1; Error: select command denied to user: [EMAIL PROTECTED] for table tmp1 also: DROP TABLE tmp1; Error:drop command denied to user: [EMAIL PROTECTED] for table tmp1 I don't want grant select and "drop" global privilege over db1 but I want use temporary tables, there is a way to do this work? Alejandro _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: MySQL 4.0.16 64bit crash report
Don,
I believe I found the bug.
MySQL/InnoDB-4.0.17, December xx, 2003
* Fixed a bug: if you created a column prefix secondary index and updated it
so that the last characters in the column prefix were spaces, InnoDB would
assert in row0upd.c, line 713. The same assertion failed if you updated a
column in an ordinary secondary index so that the new value was
alphabetically equivalent, but had a different length. This could happen,
for example, in the utf-8 character set if you updated a letter to its
accented or umlaut form.
This also explains the assertion reported by Bruce Dembecki with a column
prefix index. This fixes also a bug reported by someone about updating utf-8
accent characters, I do not remember who made that bug report.
Please test with 4.0.17 when it comes out.
Thank you,
Heikki
- Alkuperäinen viesti -
Lähettäjä: Don MacAskill [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Kopio: [EMAIL PROTECTED]
Lähetetty: Thursday, December 11, 2003 2:29 AM
Aihe: Re: MySQL 4.0.16 64bit crash report
Hi Heikki,
Heikki Tuuri wrote:
Don,
it is the assertion below which fails.
Do you use FOREIGN KEY ON UPDATE CASCADE?
Nope.
Do you have any idea which query causes the crash?
I didn't, but after Googling for similar problems, I found a thread
where you had talked about a bug in a column prefix index. (here's the
message:
http://archives.neohapsis.com/archives/mysql/2003-q4/0697.html )
I was fairly sure I wasn't using any column prefix indexes, but sure
enough, there was one. I removed the index, and MySQL has stopped
crashing completely.
Now, it's entirely possible that whichever user was submitting some
query just stopped at the same time I removed that index. But prior to
my doing that, MySQL was crashing every few minutes. Probably a dozen
times within an hour or two.
I'm keeping a close eye on it, and if it happens again, I'll turn the
query log on to see if I can catch it.
Please run CHECK TABLE on suspicious tables.
Can you test on an x86 computer?
I would be happy to, but since I don't know which query is doing it, I'm
not sure it'd be much use. I can't easily put an x86 box into
production to get the same load on it.
Have you changed the default character set of the server in my.cnf?
Nope.
Can you please send me your my.cnf.
Here it is. I actually had the innodb buffer set to 4G before, and
lowered it to see if we were still hitting some old 32bit limit or
something. Of course, there was no change, the crashes still occurred.
FWIW, this on Red Hat Enterprise 3 for AMD64.
---
[mysqld]
datadir = /xserve1/mysql
port= 3306
socket = /tmp/mysql.sock
pid-file= /xserve1/mysql/zeus.pid
skip-external-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=10M
set-variable= table_cache=256
set-variable= sort_buffer=1M
set-variable= read_buffer_size=1M
set-variable= thread_cache=8
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=512M
set-variable= query_cache_size=512M
set-variable= query_cache_type=1
set-variable= max_connections=500
set-variable= long_query_time=1
log-bin
server-id=1
innodb_data_home_dir = /xserve1/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /xserve1/mysql/
innodb_log_arch_dir = /xserve1/mysql/
set-variable = innodb_buffer_pool_size=2G
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=512M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
log-slow-queries
log-error
open-files-limit=8192
ft_min_word_len=3
max_connect_errors = 10
---
Thanks for your reply! Please let me know if there's anything else I
can do. I'm happy to help test and debug.
Don
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up
MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
..
Builds an update vector from those fields which in a secondary index
entry
differ from a record that has the equal ordering fields. NOTE: we
compare
the fields as binary strings! */
upd_t*
row_upd_build_sec_rec_difference_binary(
/**/
/* out, own: update vector of differing
fields */
dict_index_t* index, /* in: index */
dtuple_t* entry, /* in: entry to insert */
rec_t* rec,/* in: secondary index record */
mem_heap_t* heap) /* in: memory heap from which allocated
*/
{
upd_field_t*upd_field;
dfield_t* dfield;
RE: Solved - median (was Re: mean/median/mode)
Using your exact dataset listed at the bottom, I tried using derived
tables to combine your implementation under Alpha 4.1.1 as a single
statement. I expected a message saying it was to complex or out of some
resource but the server goes to 100 percent cpu and I have to kill it. I
even got it to crash once. Funny thing is after I kill it my
client(mysqlcc) gets the correct result setgo figure. Thanks for the
post, I really like your histogram example.
Using mysqld-nt Alpha 4.1.1 on WinXP, Dell 2.6ghz 600mg ram.
Thanks,
Ed
SELECT s.name, AVG(s.val) AS median
FROM (SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2) AS s
GROUP BY s.name
-Original Message-
On Friday, December 5, Robert Citek wrote:
On Wednesday, December 3, 2003, at 06:27 PM, Robert Citek wrote:
How can I calculate the mean/median/mode from a set of data using
SQL?
After a bit of googling, I found this link:
http://mysql.progen.com.tr/doc/en/Group_by_functions.html
and a few answers in the comments towards the bottom.
Below I've included a sample table and the solution I used to
calculate
the median.
Regards,
- Robert
-
DROP TABLE IF EXISTS data;
CREATE TABLE data (
name char(1) default NULL,
val int(5) default NULL
) TYPE=MyISAM;
INSERT INTO data VALUES
('a',2), ('a',2), ('a',2), ('a',2), ('a',20),
('b',4), ('b',4), ('b',4), ('b',4), ('b',40);
CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING ((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val) =
floor((COUNT(*)
+1)/2)) and
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val) = floor((COUNT(*)
+1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
Output
+--++
| name | median |
+--++
| a| 2. |
| b| 4. |
+--++
Robert,
I don't believe this is correct. I think it only appears correct due to
the particular nature of your sample data. Try it with different data
to see what I mean. For example,
DROP TABLE IF EXISTS data;
CREATE TABLE data (name char(1) default NULL, val int default NULL);
INSERT INTO data
VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6),
('a',7),
('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20);
INSERT INTO data VALUES ('b',2), ('b',3), ('b',4), ('b',5);
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING
((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val) = floor((COUNT(*) +1)/2))
AND
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val) = floor((COUNT(*) +1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
Output
+--++
| name | median |
+--++
| a| 6.5000 |
| b| 3.5000 |
+--++
As there are an odd number of values with name = 'a', we should choose
the middle value, 7, as the median, but we got 6.5. (I'm assuming we
agree that the definition of median is the middle value for N odd and
the average of the 2 middle values for N even.)
I tried to see if I could tweak your query to get the right result, but,
between the complexity of your HAVING clause and the wrinkle of getting
separate answers for each value in the name column, I just couldn't wrap
my head around it. So, I went looking and found a supposed solution at
http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html.
It is overly complex, purports to get an answer in a single select, and
is simply wrong in the case where there are an even number of values.
(The author also gives a definition of statistical median, as opposed to
financial median, which doesn't match my Intro Stats book.) But I
understood what it was trying to do and came up with the following:
To get the median of the values in a column:
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians
SELECT x.val medians
FROM data x, data y
GROUP BY x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2;
SELECT AVG(medians) AS median FROM medians;
Output using my sample data given above
++
| median |
++
| 5. |
++
To get the median of the values in a column for each value in
another column:
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians
SELECT x.name, x.val medians
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2;
SELECT name, AVG(medians) AS median FROM medians GROUP BY name;
Output using my sample data given above
+--++
| name | median |
+--++
| a| 7. |
| b| 3.5000 |
+--++
I've tested these with several different data sets, so I'm fairly
Re: MySql died a hard death after using grant and won't restart
Thanks for the reply Gerald. I took your advise and tried myisamchk with the following results: [EMAIL PROTECTED] mysql]# myisamchk -r user.* myisamchk: error: 'user.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: 'user.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair - - recovering (with sort) MyISAM-table 'user.MYI' Data records: 6 - Fixing index 1 Being relatively new to mysql, I am just getting my actual databases/tables set up so wiping out the entire mysql database and starting over is an option. Any thoughts on where I go from here?? I tried the -o option same results. Here are the results from myisamchk -r for the entire mysql directory. The results don't make sense to me. How could everything need to be rebuilt?? [EMAIL PROTECTED] mysql]# myisamchk -r *.* myisamchk: error: 'columns_priv.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: -1 when opening MyISAM-table 'columns_priv.MYD' - - recovering (with keycache) MyISAM-table 'columns_priv.MYI' Data records: 0 - myisamchk: error: 'db.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: 'db.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair - - recovering (with sort) MyISAM-table 'db.MYI' Data records: 3 - Fixing index 1 - Fixing index 2 - myisamchk: error: 'func.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: -1 when opening MyISAM-table 'func.MYD' - - recovering (with keycache) MyISAM-table 'func.MYI' Data records: 0 - myisamchk: error: 'host.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: -1 when opening MyISAM-table 'host.MYD' - - recovering (with keycache) MyISAM-table 'host.MYI' Data records: 0 - myisamchk: error: 'tables_priv.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: -1 when opening MyISAM-table 'tables_priv.MYD' - - recovering (with keycache) MyISAM-table 'tables_priv.MYI' Data records: 0 - myisamchk: error: 'user.frm' doesn't have a correct index definition. You need to recreate it before you can do a repair - myisamchk: error: 'user.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair - - recovering (with sort) MyISAM-table 'user.MYI' Data records: 6 - Fixing index 1 [EMAIL PROTECTED] mysql]# Any help would be appreciated!! -- David C. Rankin, J.D., P.E. RANKIN * BERTIN, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- - Original Message - From: gerald_clark [EMAIL PROTECTED] To: David Rankin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, December 12, 2003 8:37 AM Subject: Re: MySql died a hard death after using grant and won't restart perror 145 145 = Table was marked as crashed and should be repaired. Run myisamchk on it. David Rankin wrote: I can't figure this out. I'm setting privileges for access on a local net to a user [EMAIL PROTECTED] using grant and all of a sudden mysql is dead. I'm running 3.23.31 on Mandrake 7.2. I haven't had any problems in years. Anybody got any thoughts on this? Please reply to [EMAIL PROTECTED] What in the heck could cause a Bogus stack limit or frame pointer, aborting backtrace ?? The applicable part of the .err log is: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 031211 20:38:21 mysqld restarted 031211 20:38:21 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld: ready for connections 031211 21:40:40 /usr/sbin/mysqld: Normal shutdown 031211 21:40:40 /usr/sbin/mysqld: Shutdown Complete 031211 21:40:40 mysqld ended 031211 21:40:47 mysqld started 031211 21:40:47 /usr/sbin/mysqld: Can't open file: 'user.MYD'. (errno: 145) 031211 21:40:47 mysqld ended next I mv /datadir/mysql /datadir/mysql-old then do a mysql_install_db and try again. Same error results mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable
Re: MySql died a hard death after using grant and won't restart
Gerald, I spoke too soon. myisamchk seemed to have worked !!! I now have mysql running again and it seems happy. Now if I can just figure out the privileges to put into the db table to allow my local users to be able to connect though their browsers, I'll really have made progress. Thank you for your help! -- David C. Rankin, J.D., P.E. RANKIN * BERTIN, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- - Original Message - From: gerald_clark [EMAIL PROTECTED] To: David Rankin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, December 12, 2003 8:37 AM Subject: Re: MySql died a hard death after using grant and won't restart perror 145 145 = Table was marked as crashed and should be repaired. Run myisamchk on it. David Rankin wrote: I can't figure this out. I'm setting privileges for access on a local net to a user [EMAIL PROTECTED] using grant and all of a sudden mysql is dead. I'm running 3.23.31 on Mandrake 7.2. I haven't had any problems in years. Anybody got any thoughts on this? Please reply to [EMAIL PROTECTED] What in the heck could cause a Bogus stack limit or frame pointer, aborting backtrace ?? The applicable part of the .err log is: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 031211 20:38:21 mysqld restarted 031211 20:38:21 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld: ready for connections 031211 21:40:40 /usr/sbin/mysqld: Normal shutdown 031211 21:40:40 /usr/sbin/mysqld: Shutdown Complete 031211 21:40:40 mysqld ended 031211 21:40:47 mysqld started 031211 21:40:47 /usr/sbin/mysqld: Can't open file: 'user.MYD'. (errno: 145) 031211 21:40:47 mysqld ended next I mv /datadir/mysql /datadir/mysql-old then do a mysql_install_db and try again. Same error results mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Cannot determine thread, ebp=0xb, backtrace may not be correct Bogus stack limit or frame pointer, aborting backtrace -- David C. Rankin, J.D., P.E. Rankin * Bertin, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to keep multiple instances of the same information from being displayed
Hi Jess
Have you tried SELECT DISTINCT
not sure if 'select distinct *' will work but if doesn't 'select
distinct userlastname,userfirstname' will
hope this helps
Steve Davies
Hunter, Jess wrote:
Using PHP as the front end I am creating a form with a dropdown box that
displays information from one table to be inserted into another table.
That's PHP and I have gotten that part down with no issue.
However, in the table a person could be listed multiple times and I only
want a user to be listed a single time. for instance in the table I may have
Jones, Jim
Jones, Mary
Jones, Mary
Jones, Mary
Jones, Nancy
Jones, Paul
when I do my SELECT I only want Mary Jones to show up a single time.
Here is the code I am currently using within the Dropdown box:
SNIPPET
$Link = mysql_connect($Host, $User, $Password);
$Query=SELECT * from $TableName ORDER BY userlastname;
$Result= mysql_db_query ($DBName, $Query, $Link);
while ($Row = mysql_fetch_array ($Result)){
print (option value='$Row[userlastname],
$Row[userfirstname]'$Row[userlastname], $Row[userfirstname]/option);
}
mysql_close ($Link);
?
/SNIPPET
As you can see I am pulling two fields from one table, then combining them
to make a single field in another table. I am doing this to provide
continuity when I create queries in the future.
Any help with this would be greatly appreciated
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excluding Tables from mysqldump
We have 2 tables which are roughly half the size of the entire database
{ ~1.5GB}. These 2 tables are rarely changed {1-2 times a quarter}.
Is there a way in mysqldump to exclude these two tables without
specifically naming all of the tables { 100} we want to include?
Re: MySql died a hard death after using grant and won't restart
You might consider using Grant instead of editing the permissions table manually. Follow this link for the appropriate section of the manual: http://www.mysql.com/doc/en/GRANT.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Login Problems on 4.1
Hi guys! I am facing some problems trying to login user to the data base server using --any-- client(except the mysql cli) to the server. Users using no password does not seems to have any problems. It happens that when a client tries to log into the db server using passwords the server doesn't seems to authorize. Even the most recent MyCC client fails to autorize users using passwords. Do I need to compile the clients against the new server's library? The MyCC client was linked againts the 4.0.16 server. was there any change in the auth-protocol? Best Regards -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub select equivalent
I am using version 3.23.58 and need to do the following: select * from a where id not in (select tbl_id from b where tbl=a); Given that my version does not support sub selects, how can I re-write the statement to get the desired results? Thanks. Dean Hoover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sporadic myisam table corruption.
I'm having sporadic myisam table corruption. This table is constantly being added to, updated, and deleted from. PHPMyAdmin reports that The table is in use when I try to access this table after corruption. After I perform this step in the correct directory everything goes back to normal. myisamchk --recover troubled_table After reading the mysql manual page I changed all the VARCHAR fields to CHAR fields, hoping my problem would dissapear. This change has had no effect on my problem. The only things in the error log are start ups and shutdowns performed nightly. Here's the table definition. CREATE TABLE troubled_table ( office smallint(4) unsigned NOT NULL default '0', ticket_id char(30) NOT NULL default '', item_quantity mediumint(8) unsigned NOT NULL default '0', from_face char(30) NOT NULL default '', from_down decimal(6,1) unsigned NOT NULL default '0.0', from_up decimal(6,1) unsigned NOT NULL default '0.0', from_depth decimal(6,1) unsigned NOT NULL default '0.0', to_face char(30) NOT NULL default '', to_down decimal(6,1) unsigned NOT NULL default '0.0', to_up decimal(6,1) unsigned NOT NULL default '0.0', to_depth decimal(6,1) unsigned NOT NULL default '0.0', associated_document char(30) NOT NULL default '', PRIMARY KEY (office,ticket_id), KEY move_id (ticket_id) ) TYPE=MyISAM COMMENT='Material Move Orders'; mysql --version says: mysql Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686) What can I try now? James Hicks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sub select equivalent
left joins are your key select * from a left join b on id = tbl_id and b.tbl = a where b.tbl_id is null (not 100% sure on my syntax, but note the join, and the limitation on 'left' result set is specified within the left join clause) -Original Message- From: Dean A. Hoover [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 2:04 PM To: [EMAIL PROTECTED] Subject: sub select equivalent I am using version 3.23.58 and need to do the following: select * from a where id not in (select tbl_id from b where tbl=a); Given that my version does not support sub selects, how can I re-write the statement to get the desired results? Thanks. Dean Hoover -- 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: Multiple languages in the same column
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yayati Kasralikar wrote: We are using MySQL 4.1.1 with mysql-connector-java-3.1 JDBC Driver(nightly snapshot). The only way we can store and display the Unicode content is by specifying it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 AND specifying the table/column type as CHARACTER SET utf8. e.g.: create table test_table (col1 VARCHAR(10) CHARACTER SET utf8) None of the followings we tried work, if we do not specify it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 1.We tried to set database's default character set to UTF-8 like: mysqlalter database database_name default character set utf8; 2.We tried to specify the table/column type as CHARACTER SET utf8 3.We tried to set the default character set to utf-8 in the my.ini by adding the following line: default-character-set=utf8 Is my understanding correct? Yes. If you are going to mix character sets in the queries you are _sending_ to the server from the JDBC driver, then you need to tell the client to use UTF-8, like you have in your URL. I'll work on adding the ability for the driver to autodetect when your server's 'charset_client' is 'UTF-8'. -Mark - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/2hUYtvXNTca6JD8RAhwoAKCKiK2No/++X2A6xqIRl0QuEcymbQCfSiQ+ fXuh3fYyeTJ97DAVIDGstOM= =ocUZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sporadic myisam table corruption.
Change myisam format to something else, like InoDB or BDB Best Regards! On Fri, 2003-12-12 at 15:05, James E Hicks III wrote: I'm having sporadic myisam table corruption. This table is constantly being added to, updated, and deleted from. PHPMyAdmin reports that The table is in use when I try to access this table after corruption. After I perform this step in the correct directory everything goes back to normal. myisamchk --recover troubled_table After reading the mysql manual page I changed all the VARCHAR fields to CHAR fields, hoping my problem would dissapear. This change has had no effect on my problem. The only things in the error log are start ups and shutdowns performed nightly. Here's the table definition. CREATE TABLE troubled_table ( office smallint(4) unsigned NOT NULL default '0', ticket_id char(30) NOT NULL default '', item_quantity mediumint(8) unsigned NOT NULL default '0', from_face char(30) NOT NULL default '', from_down decimal(6,1) unsigned NOT NULL default '0.0', from_up decimal(6,1) unsigned NOT NULL default '0.0', from_depth decimal(6,1) unsigned NOT NULL default '0.0', to_face char(30) NOT NULL default '', to_down decimal(6,1) unsigned NOT NULL default '0.0', to_up decimal(6,1) unsigned NOT NULL default '0.0', to_depth decimal(6,1) unsigned NOT NULL default '0.0', associated_document char(30) NOT NULL default '', PRIMARY KEY (office,ticket_id), KEY move_id (ticket_id) ) TYPE=MyISAM COMMENT='Material Move Orders'; mysql --version says: mysql Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686) What can I try now? James Hicks -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting a postgres database to MySQL
Is there a simple way of copying a postgres database to MySQL? I was hoping that postgres's backup 'pg_dump' utility (which dumps a database into a text file in such a format that it can be easily recreated) might be compatible with MySQL's backup and restore facilities. Zenzo wrote: i think you can because the dump file is a sql file and it is universal for both exception that mysql hasn't subselects. If you havent subselects it should work Thanks Zenzo and sorry to take a couple of days to get back to you (I've been ill). Unfortunately you can't use a postgres pg_dump created file to import a database into MySQL. There are too many incompatibilities, such as the 'boolean' type, varchar being limited to 255 chars (in MySQL), SERIAL (for the primary key) and probably a few more. Also pg_dump is designed to create output that when used will exactly create a postgres database of the database that was 'dumped' in the first place, so quite a lot of the pg_dumped formatting is postgres specific. It didn't take too long to alter and create the tables for MySQL and then to write the postgres database's data in such a way as to import them into MySQL using 'LOAD DATA LOCAL INFILE...'. Thanks and regards, ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql died a hard death after using grant and won't restart - FIXED!!
Thanks Gerald! I'm replying to my self for the benefit of anyone else who has the mysql user.MYD file get corrupted. As root change to the mysql/mysql directory. run myisamchk -r *.* Ignore the errors. Start mysql. Pray your mysql root password isn't corrupt and your through. My root password was corrupt, but thank God I had created a second super user account and was able to fix the root password in the user table from the second account --- whew.. -- David C. Rankin, J.D., P.E. RANKIN * BERTIN, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- - Original Message - From: gerald_clark [EMAIL PROTECTED] To: David Rankin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, December 12, 2003 8:37 AM Subject: Re: MySql died a hard death after using grant and won't restart perror 145 145 = Table was marked as crashed and should be repaired. Run myisamchk on it. David Rankin wrote: I can't figure this out. I'm setting privileges for access on a local net to a user [EMAIL PROTECTED] using grant and all of a sudden mysql is dead. I'm running 3.23.31 on Mandrake 7.2. I haven't had any problems in years. Anybody got any thoughts on this? Please reply to [EMAIL PROTECTED] What in the heck could cause a Bogus stack limit or frame pointer, aborting backtrace ?? The applicable part of the .err log is: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 031211 20:38:21 mysqld restarted 031211 20:38:21 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld: ready for connections 031211 21:40:40 /usr/sbin/mysqld: Normal shutdown 031211 21:40:40 /usr/sbin/mysqld: Shutdown Complete 031211 21:40:40 mysqld ended 031211 21:40:47 mysqld started 031211 21:40:47 /usr/sbin/mysqld: Can't open file: 'user.MYD'. (errno: 145) 031211 21:40:47 mysqld ended next I mv /datadir/mysql /datadir/mysql-old then do a mysql_install_db and try again. Same error results mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Cannot determine thread, ebp=0xb, backtrace may not be correct Bogus stack limit or frame pointer, aborting backtrace -- David C. Rankin, J.D., P.E. Rankin * Bertin, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.16 64bit crash report
Hi Heikki,
Thanks so much for taking the time to look into this. No wonder you
have such a great product.
When 4.0.17 comes out, I'll certainly test it.
Thanks again,
Don
Heikki Tuuri wrote:
Don,
I believe I found the bug.
MySQL/InnoDB-4.0.17, December xx, 2003
* Fixed a bug: if you created a column prefix secondary index and updated it
so that the last characters in the column prefix were spaces, InnoDB would
assert in row0upd.c, line 713. The same assertion failed if you updated a
column in an ordinary secondary index so that the new value was
alphabetically equivalent, but had a different length. This could happen,
for example, in the utf-8 character set if you updated a letter to its
accented or umlaut form.
This also explains the assertion reported by Bruce Dembecki with a column
prefix index. This fixes also a bug reported by someone about updating utf-8
accent characters, I do not remember who made that bug report.
Please test with 4.0.17 when it comes out.
Thank you,
Heikki
- Alkuperäinen viesti -
Lähettäjä: Don MacAskill [EMAIL PROTECTED]
Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]
Kopio: [EMAIL PROTECTED]
Lähetetty: Thursday, December 11, 2003 2:29 AM
Aihe: Re: MySQL 4.0.16 64bit crash report
Hi Heikki,
Heikki Tuuri wrote:
Don,
it is the assertion below which fails.
Do you use FOREIGN KEY ON UPDATE CASCADE?
Nope.
Do you have any idea which query causes the crash?
I didn't, but after Googling for similar problems, I found a thread
where you had talked about a bug in a column prefix index. (here's the
message:
http://archives.neohapsis.com/archives/mysql/2003-q4/0697.html )
I was fairly sure I wasn't using any column prefix indexes, but sure
enough, there was one. I removed the index, and MySQL has stopped
crashing completely.
Now, it's entirely possible that whichever user was submitting some
query just stopped at the same time I removed that index. But prior to
my doing that, MySQL was crashing every few minutes. Probably a dozen
times within an hour or two.
I'm keeping a close eye on it, and if it happens again, I'll turn the
query log on to see if I can catch it.
Please run CHECK TABLE on suspicious tables.
Can you test on an x86 computer?
I would be happy to, but since I don't know which query is doing it, I'm
not sure it'd be much use. I can't easily put an x86 box into
production to get the same load on it.
Have you changed the default character set of the server in my.cnf?
Nope.
Can you please send me your my.cnf.
Here it is. I actually had the innodb buffer set to 4G before, and
lowered it to see if we were still hitting some old 32bit limit or
something. Of course, there was no change, the crashes still occurred.
FWIW, this on Red Hat Enterprise 3 for AMD64.
---
[mysqld]
datadir = /xserve1/mysql
port= 3306
socket = /tmp/mysql.sock
pid-file= /xserve1/mysql/zeus.pid
skip-external-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=10M
set-variable= table_cache=256
set-variable= sort_buffer=1M
set-variable= read_buffer_size=1M
set-variable= thread_cache=8
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=512M
set-variable= query_cache_size=512M
set-variable= query_cache_type=1
set-variable= max_connections=500
set-variable= long_query_time=1
log-bin
server-id=1
innodb_data_home_dir = /xserve1/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /xserve1/mysql/
innodb_log_arch_dir = /xserve1/mysql/
set-variable = innodb_buffer_pool_size=2G
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=512M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
log-slow-queries
log-error
open-files-limit=8192
ft_min_word_len=3
max_connect_errors = 10
---
Thanks for your reply! Please let me know if there's anything else I
can do. I'm happy to help test and debug.
Don
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - hot backup tool for InnoDB which also backs up
MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
..
Builds an update vector from those fields which in a secondary index
entry
differ from a record that has the equal ordering fields. NOTE: we
compare
the fields as binary strings! */
upd_t*
row_upd_build_sec_rec_difference_binary(
/**/
/* out, own: update vector of differing
fields */
dict_index_t* index, /* in: index */
dtuple_t* entry, /* in: entry to insert */
rec_t* rec,/* in: secondary index record */
mem_heap_t* heap) /* in: memory heap from which allocated
*/
{
upd_field_t*
storing .tar files in mysql
Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can create the database and tables, but I can't seem to insert the .tar file properly...Any pointers would be appreicated... Thanks, Jake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can create the database and tables, but I can't seem to insert the .tar file properly...Any pointers would be appreicated... Thanks, Jake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing .tar files in mysql
On Fri, Dec 12, 2003 at 02:54:44PM -0600, [EMAIL PROTECTED] wrote: I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most I believe the data type you are looking for is blob. However, you would be better off using your row to point to a file located on the hard drive instead of actually in the database. I can't recall the technical details but, your performance will be much better that way. -- Neil Watson | Gentoo Linux Network Administrator | Uptime 1 day http://watson-wilson.ca | 2.4.23 AMD Athlon(tm) MP 2000+ x 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
The scenario we wish to accomplish SERVER1 - Logging DB1 SERVER2 - Logging DB2 Logging DB3 Replicating DB1 from SERVER1 - Logging DB1 SERVER3 - Replicating DB1 from SERVER2 Replicating DB2 from SERVER2 Replicating DB3 from SERVER2 What I am asking is for confirmation that the following my.cnf files would do that. SERVER1 [mysqld] log-bin binlog-do-db=DB1 server-id=1 SERVER2 [mysqld] log-bin master-host=SERVER1 master-user=SERVER2 master-password=password binlog-do-db=DB2 binlog-do-db=DB3 log-slave-updates server-id=2 SERVER3 [mysqld] master-host=SERVER2 master-user=SERVER3 master-password=password binlog-do-db=DB1 binlog-do-db=DB2 binlog-do-db=DB3 server-id=3 To do the initial setup of the slaves I would do the following? Dump DB1 from SERVER1 Start logging of DB1 on SERVER1 Load Dump of DB1 onto SERVER2 Start replication of DB1 on SERVER2 (add replication parameters to my.cnf and restart) Dump DB2 from SERVER2 Dump DB3 from SERVER2 Load Dump of DB1 onto SERVER3 Load Dump of DB2 onto SERVER3 Load Dump of DB3 onto SERVER3 Start replication of DB1 on SERVER3 (add replication parameters to my.cnf and restart) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
I am working with a project on sourceforge http://leopard.sourceforge.net and this is one of the package management stratagies we are thinking about trying. As I said I have almost no experience with mysql so I open to any and all suggestions. Very good points being made about the size of the rows. Thanks for the quick responses :-) Jake Walters Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can create the database and tables, but I can't seem to insert the .tar file properly...Any pointers would be appreicated... Thanks, Jake -- 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]
plz help a newbie
Hi there just a lot lot lot of questions. Plz answer as many as you can. What do u prefer varchar(2) or tiny_text? What is the biggest size for the varchar? If I want to store a very big article. What is the biggest size for text that is being support? What is the difference between timestamp and DateTime? What do u suggest? What is the difference between Varchar and Binary Varchar? Details plz? I have these type of associations supported by mysql 1:1 1:1 (Non-Identyfying) 1:1 (Descendent Obj.) What are the differences among them? Especially the last one? - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: storing .tar files in mysql
On Fri, Dec 12, 2003 at 02:54:44PM -0600, [EMAIL PROTECTED] wrote: Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. Just follow those instructions. MySQL doesn't care if it's .tar or .jpg file. It's all just bits. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 89 days, processed 3,627,693,957 queries (466/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: plz help a newbie
Here is a link to the MySQL manual. Lots of good info here. http://www.mysql.com/documentation/mysql/bychapter/index.htm l The max size of a varchar is 255 characters. You will want to use one of the text data types if you are going to store long articles. I suggest mediumtext. You can find the exact maximum sizes in the manual under the data types section. A timestamp column gets automatically updated to now on inserts and updates unless you explictly set it to something. Datetime fields just store a datetime (you update them to whatever you want). I don't understand what you meant by this. Can you give an example? I have these type of associations supported by mysql 1:1 1:1 (Non-Identyfying) 1:1 (Descendent Obj.) What are the differences among them? Especially the last one? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange warning when using an IF statement
I keep getting the following error when I try to run an if statement
Warning: 2 is not a valid MySQL-Link resource in then give the filename
Here is what I am trying to do.
if ($bumpnumber4) {
print (display this);
}else {
print (display that);
}
mysql_close ($Link);
Anyone have any idea what I may be doing wrong?
TIA
Jess
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.550 / Virus Database: 342 - Release Date: 12/9/03
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple languages in the same column
Hello Mark, Thanks for your help. I have one more question. I am using the some tables with utf8 character set and some tables with latin1 character set. I am using the jdbc connection string from the properties file with the characterEncoding=UTF-8. I am not changing the jdbc connection string, but I need to access these tables with different character sets. I am getting the following error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' I can make all my tables utf8 to make my application work. Is this is only choice I have? Thank you, -Yayati -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 2:21 PM To: Yayati Kasralikar Cc: [EMAIL PROTECTED] Subject: Re: Multiple languages in the same column -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yayati Kasralikar wrote: We are using MySQL 4.1.1 with mysql-connector-java-3.1 JDBC Driver(nightly snapshot). The only way we can store and display the Unicode content is by specifying it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 AND specifying the table/column type as CHARACTER SET utf8. e.g.: create table test_table (col1 VARCHAR(10) CHARACTER SET utf8) None of the followings we tried work, if we do not specify it in the jdbc connection string(url) like: jdbc:mysql://localhost/database_name?useUnicode=truecharacterEncoding=UTF-8 1.We tried to set database's default character set to UTF-8 like: mysqlalter database database_name default character set utf8; 2.We tried to specify the table/column type as CHARACTER SET utf8 3.We tried to set the default character set to utf-8 in the my.ini by adding the following line: default-character-set=utf8 Is my understanding correct? Yes. If you are going to mix character sets in the queries you are _sending_ to the server from the JDBC driver, then you need to tell the client to use UTF-8, like you have in your URL. I'll work on adding the ability for the driver to autodetect when your server's 'charset_client' is 'UTF-8'. -Mark - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/2hUYtvXNTca6JD8RAhwoAKCKiK2No/++X2A6xqIRl0QuEcymbQCfSiQ+ fXuh3fYyeTJ97DAVIDGstOM= =ocUZ -END PGP SIGNATURE- -- 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]
large query result caching (using C API)
MySQL 4.1.0-alpha-max-nt MySQL C API interface Visual C++ 6.0 trying some query when cache is on... it causes ERROR: Lost connection to MySQL sever during query MySQL cache memory used for _this_ query ~900Kb but when cache is off or query result size is less than few Kb, everything is OK. WHY? additional information: /* my.ini BEGIN */ [WinMySQLAdmin] Server=E:/mysql/bin/mysqld-nt.exe QueryInterval=10 [mysqld] basedir = e:/mysql/ datadir = e:/mysql/data/ default-character-set=cp1251 key_buffer_size=64M tmp_table_size=16M table_cache=256 sort_buffer=16M read_buffer_size=1M read_rnd_buffer_size=1M query_cache_size=64M query_cache_limit=5M /* my.ini END */ --- Best regards, Alex. mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not Null doesn't run on Delphi
I wonder why even I already sey NOT NULL in Mysql field but Delphi (with MyODBC 3.51.06) still enable to save to the Table! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
customizing order by question
If anyone has any suggestions, they would be greatly appreciated. I've searched though my resources and online, and perhaps my newbie frustration is making me overlook something simple. What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter. Any ideas? _ Tired of slow downloads and busy signals? Get a high-speed Internet connection! Comparison-shop your local high-speed providers here. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customizing order by question
Brandyn Riffle wrote: What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter. First, the correct solution would probably be to store your event date in an actual DateTime column. Then MySql would know how to sort it properly. And you could still get the seperate pieces out easily. For example if you had a EventDate column of type DateTime, then you could do select year(EventDate) as Year, monthname(EventDate) as month, dayofmonth( EventDate) as Day, dayofweek(EventDate) as WeekDay from MyTable and you'd get back columns like: |year|month |day|WeekDay| |2003|December| 12|Friday | -- So, that would be the RIGHT way to do it. But, you can make do with what you've got as well. I'm assuming you've got a Month field that contains 3 letter month abbreviations like JAN,FEB,MAR,APR,MAY, etc. Adjust the actual abbreviation/spelling as needed: select case month when 'JAN' then 1 when 'FEB' then 2 when 'MAR' then 3 when 'APR' then 4 when 'MAY' then 5 when 'JUN' then 6 when 'JUL' then 7 when 'AUG' then 8 when 'SEP' then 9 when 'OCT' then 10 when 'NOV' then 11 when 'DEC' then 12 else 13 end as MonthNum, month,day,year from test2 order by year,monthnum The case statement here converts the month abbrev. into numbers which will sort correctly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
new install - command prompt doesn't work
I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy
Re: new install - command prompt doesn't work
At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customizing order by question
At 20:36 -0500 12/12/03, Brandyn Riffle wrote: If anyone has any suggestions, they would be greatly appreciated. I've searched though my resources and online, and perhaps my newbie frustration is making me overlook something simple. What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter. We cannot answer your question, because you have stated your requirements without telling us how your data values actually are represented. I suppose the years are stored as integers, but how are months stored? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Login Problems on 4.1
At 14:42 -0400 12/12/03, Victor Medina wrote: Hi guys! I am facing some problems trying to login user to the data base server using --any-- client(except the mysql cli) to the server. Users using no password does not seems to have any problems. It happens that when a client tries to log into the db server using passwords the server doesn't seems to authorize. Even the most recent MyCC client fails to autorize users using passwords. Do I need to compile the clients against the new server's library? The MyCC client was linked againts the 4.0.16 server. was there any change in the auth-protocol? Indeed there was. This is mentioned in the upgrading to 4.1 section in the manual. Some links you may want to check out: http://www.mysql.com/doc/en/Upgrading-from-4.0.html http://www.mysql.com/doc/en/Upgrading-grant-tables.html http://www.mysql.com/doc/en/Password_hashing.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub select equivalent
At 14:03 -0500 12/12/03, Dean A. Hoover wrote: I am using version 3.23.58 and need to do the following: select * from a where id not in (select tbl_id from b where tbl=a); Given that my version does not support sub selects, how can I re-write the statement to get the desired results? http://www.mysql.com/doc/en/Rewriting_subqueries.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Login Problems on 4.1
At 14:42 -0400 12/12/03, Victor Medina wrote: It happens that when a client tries to log into the db server using passwords the server doesn't seems to authorize. Even the most recent MyCC client fails to autorize users using passwords. Do I need to compile the clients against the new server's library? The MyCC client was linked againts the 4.0.16 server. was there any change in the auth-protocol? 4.1 changed the authentication code. You can tell mysqld to use the old authentication by starting it with the --old-passwords option (or add old-passwords to the [mysqld] group in my.cnf). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customizing order by question
Chuck Gadd wrote:
Brandyn Riffle wrote:
What I'm trying to do is sort by a column with by pre-set criteria;
I've a political database with events with columns for the year,
month, day, and event. I'd like to order by months, (e.g. JAN, FEB,
MAR, etc...) after sorting by year. The sorting by year part was
easy... the months are another matter.
First, the correct solution would probably be to store your event date
in an actual DateTime column. Then MySql would know how to sort it
properly. And you could still get the seperate pieces out easily.
For example if you had a EventDate column of type DateTime, then you
could do
select year(EventDate) as Year, monthname(EventDate) as month,
dayofmonth( EventDate) as Day, dayofweek(EventDate) as WeekDay from MyTable
and you'd get back columns like:
|year|month |day|WeekDay|
|2003|December| 12|Friday |
--
So, that would be the RIGHT way to do it. But, you can make do with
what you've got as well.
I'm assuming you've got a Month field that contains 3 letter month
abbreviations like JAN,FEB,MAR,APR,MAY, etc. Adjust the actual
abbreviation/spelling as needed:
select case month
when 'JAN' then 1
when 'FEB' then 2
when 'MAR' then 3
when 'APR' then 4
when 'MAY' then 5
when 'JUN' then 6
when 'JUL' then 7
when 'AUG' then 8
when 'SEP' then 9
when 'OCT' then 10
when 'NOV' then 11
when 'DEC' then 12
else 13 end
as MonthNum, month,day,year
from test2
order by year,monthnum
The case statement here converts the month abbrev. into numbers
which will sort correctly.
I'm also assuming you have a CHAR(3) month column. I'll further pretend
your table is named events_table, with columns year and month, so
you should replace those with the correct names in what follows (to
apply to your table). If you have good reason to keep the month column,
you may wish to build the sort order into it by using enum, like this:
ALTER TABLE events_table
MODIFY month
ENUM('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
The beauty of this is that the enum column returns the given strings in
string context, but will return 1 through 12 in numeric context. So,
SELECT * from events_table ORDER BY year, month
would display the 3 letter string in the month column of the output, but
would sort according to the numerical position in the list.
See http://www.mysql.com/doc/en/ENUM.html for details.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customizing order by question
At 22:47 -0500 12/12/03, Brandyn Riffle wrote: Years are stored as smallint(4) Months are stored as tinytext converting to datetime might have been a good idea earlier, but I've over 3000 entries, so if I can avoid that I'd like to... I'm not above creating another column to correlate months to integers, and sort by that if that would work... Do you mean they're stored as the string values 'JAN', 'FEB', 'MAR', etc? If so, the natural sort order is lexical, which isn't what you want. However, you can use FIELD() to map an arbitrary set of values onto a given numeric order: ORDER BY FIELD(month,'JAN','FEB','MAR',...,'NOV','DEC') will map month values onto the numbers 1 to 12 and sort them numerically. http://www.mysql.com/doc/en/String_functions.html From: Paul DuBois [EMAIL PROTECTED] To: Brandyn Riffle [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: customizing order by question Date: Fri, 12 Dec 2003 20:44:04 -0600 At 20:36 -0500 12/12/03, Brandyn Riffle wrote: If anyone has any suggestions, they would be greatly appreciated. I've searched though my resources and online, and perhaps my newbie frustration is making me overlook something simple. What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter. We cannot answer your question, because you have stated your requirements without telling us how your data values actually are represented. I suppose the years are stored as integers, but how are months stored? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions about MySQL implementation
Hi all! I've got a few questions that I was hoping some of the fine readers of this list could help me out with. I'll probably be going into a development meeting this coming week and will need to have some information up my sleave to ensure the mighty MySQL is selected as the database backend for the application being developed. 1. We all know that InnoDB can be backed up hot (by various means). I know that there are a few MS SQL Server (ick) and DB2 lovers in the group I'll be meeting with this week. I also know that these two databases do a form of online backup. Given that they are not multiversioned, how on earth do they actually provide this functionality? I guess this ties in with how they implement the READ REPEATABLE isolatation level. Any comments on implementation, performance and other info would be gladly received! 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? 3. At the moment, the MySQL API seems to have a size limit of 16 MB for data sent over the wire (I have seen that the MySQL 4.1 libraries allow for sending information in chunks along with prepared statements). I take it the best method of inserting greater amounts of data into a column would be by first writing the file somewhere on the database server and using LOAD DATA? Any comments on this of any type from the learned populace of MySQL users? :-) 4. In a DB server that has 2 physical disks running MySQL 4.1.1 Alpha and utilising the multiple table space feature of InnoDB, what distribution of files (ibdata, log files, individual table space files) is likely to result in the best performace? Any insights of similar type for using MyISAM tables? All responses will be gratefully received! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Planned transactions?
Hi again all,
Given that a transaction looks like this:
BEGIN;
SELECT useless_field FROM useless_table WHERE useless_identifier =
'useless';
SELECT useless_field FROM useless_table WHERE useless_identifier =
'something else';
INSERT INTO useless_table (useless_field, useless_identifier) VALUES
('what?','huh?');
COMMIT;
Assuming the isolation level is either READ_REPEATABLE or SERIALIZABLE,
would there be any possible benefit to gain from taking the statements
that make up the entire transaction, working out what tables and columns
will be touched and then coming up with some execution policy? I take it
that at the moment, InnoDB's rollback segments grow in a fashion that is
basically a backward looking approach of what I've described - am I
correct?
Regards,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
