datadir variable issue
My configuration: I am using 4.0.16 compiled with the following configure command on RedHat Linux 9.0: > CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql-4.0.16 --enable-assembler --with-mysqld-ldflags=-all-static I have noticed a few various things in relation to the configure script and the my.cnf/command line variables. 1) If I specify a “--prefix” (for example /usr/local/mysql) and the ./configure script has a “sysconfdir” variable that points to [PREFIX/etc] why does a config file of /usr/local/mysql/etc/my.cnf not get read? Or I guess I should say why doesn’t the /etc/my.cnf location get overridden once I have specified a new location for the “sysconfdir” variable? 2) The ./configure script has two variables “datadir” and “localstatedir” that respectively point to [PREFIX/share] and [PREFIX/var]. From the configure scripts perspective the variable “localstatedir” is the location for a my.cnf file and is the directory where the databases will be stored. While the “datadir” appears to contain locale specific information. The problem I see is that the my.cnf and command line variables also have a “datadir” variable but it is the means of telling mysql where to find the directory that contains the database folder or the “localstatedir” location from the configure script thus the variable names do not match up and it proved to be very confusing. And in the manual section 4.1.2 is says you can locate a my.cnf file in DATADIR/ but it took some time to realize that this is the datadir from the mysqld command line variable not the ./configure script datadir variable. Thanks in advance for you time. Bret Jordan [EMAIL PROTECTED] -- ~~~ Bret Jordan Dean's Office Computer Administrator College of Engineering 801.585.3765 University of Utah [EMAIL PROTECTED] ~~~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlfront versus mysqlcc
It looks nice. Too bad it's not Linux, yet. Hopefully in 2004. -Marc On Fri, 2003-11-14 at 08:51, Daniel Kiss wrote: > Try SQLyog at www.sqlyog.com > > It is pretty good, and full of interesting features. > > >I currently use the excellent mysqlfront which is sadly no longer > >supported. > > > >I've tried mysqlcc but it seems non-intuitive, and missing loads of > >features. -- Marc <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RANDOM in GROUP BY
A HUGE THANK YOU
I had almost given up doing this in a query, it works!
I knew there had to be something like this, just couldn't quite
snag it most of my queries are pedestrian to say the least.
It may be inefficient --and I see why it is-- but would it not be less
so for the dbms to do this rather than using php to do essentially the
same thing afterwards (which is what I did).
Roger Baklund wrote:
* Colleen Dick
This stumped them in PHPbuilder...
I have a table lets call it moms
each row in the moms table can have many kids,
and some "kids" have >1 "mom". I have a kid table
and I have a mxk map table to relate them.
what I want to do is select all the moms and for each mom I want a
random one of its kids to go with it.
SELECT moms.m_id,m_name,kids.k_id,k_name FROM
moms, mxk, kids WHERE
moms.m_id = mxk.m_id
AND mxk.k_id = kids.k_id
GROUP BY moms.m_id
Try this:
SELECT moms.m_id,m_name,
MAX(CONCAT(RAND(),'|',kids.k_id,'|',k_name)) AS data
FROM moms
NATURAL JOIN mxk
NATURAL JOIN kids
GROUP BY moms.m_id, m_name
The MAX(CONCAT-thingy is known as the MAX-CONCAT trick, it is described
here:
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >
This is relatively heavy on the server, but this may not be significant if
the amount of data is small, i.e. a few thousand moms & kids should not be a
problem.
The natural join uses the column names to join, it seems this can be used in
your case, because the columns have the same name in the joined table. This
eliminates the need of a where clause in this case. The MAX-CONCAT trick
will also work with 'normal' joins, the way you had in your example.
You would need to split the data column in your application, in PHP it could
be something like this:
list($dummy, $k_id, $k_name) = explode("|",$myrow["data"]);
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlϵÄÒ»¸öСÎÊÌâ¡£^_^
请问，怎么样把文本文件解析出来，然后写入数据库！？就是：C当中的解析器。 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: RANDOM in GROUP BY
* Colleen Dick
> This stumped them in PHPbuilder...
>
> I have a table lets call it moms
> each row in the moms table can have many kids,
> and some "kids" have >1 "mom". I have a kid table
> and I have a mxk map table to relate them.
>
> what I want to do is select all the moms and for each mom I want a
> random one of its kids to go with it.
>
>
> SELECT moms.m_id,m_name,kids.k_id,k_name FROM
> moms, mxk, kids WHERE
> moms.m_id = mxk.m_id
> AND mxk.k_id = kids.k_id
> GROUP BY moms.m_id
Try this:
SELECT moms.m_id,m_name,
MAX(CONCAT(RAND(),'|',kids.k_id,'|',k_name)) AS data
FROM moms
NATURAL JOIN mxk
NATURAL JOIN kids
GROUP BY moms.m_id, m_name
The MAX(CONCAT-thingy is known as the MAX-CONCAT trick, it is described
here:
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >
This is relatively heavy on the server, but this may not be significant if
the amount of data is small, i.e. a few thousand moms & kids should not be a
problem.
The natural join uses the column names to join, it seems this can be used in
your case, because the columns have the same name in the joined table. This
eliminates the need of a where clause in this case. The MAX-CONCAT trick
will also work with 'normal' joins, the way you had in your example.
You would need to split the data column in your application, in PHP it could
be something like this:
list($dummy, $k_id, $k_name) = explode("|",$myrow["data"]);
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is mysqladmin part of Os X package
From: Hanon Sinay <[EMAIL PROTECTED]> Date: Friday Nov 14, 2003 12:24:44 PM America/Los_Angeles Subject: Unable to open and run "mysqladmin" Mac Os X File Edit Options Buffers Tools Help SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `<' and `>'). SEND-PR: From: Hanon Sinay To: [EMAIL PROTECTED] Subject: Unable to open and run "mysqladmin", Mac Os X Description: I am trying to use "mysqlbug script" in a conforming way as requested. I may not have it right. Sorry! (I am obviuosly a new user. I am trying.) Unable to open and run "mysqladmin" from the terminal window shell command line, Mac Os X 10.2.8. I get no error messages. I simply get the next line prompt for a new shell command. I believe the file is not present. The manual and installation documentation all refer to the presence of "mysqladmin". Is "mysqladmin" supposed to be present in the Mac Os X package? Am I looking for a fie that is not supposed to be present in the package? I am not running Mac Os X Server. I use: Mac osX v 10.2.8 (jaguar) on Mac G-4, with 940 mb ram mysql version 4.0.15, installed from download package "mysql-standard-4.0.15.dmg" mysql startup installed from download package "MySQLStartupItem.pkg" Installation was "successful" with no apparent problems (installation this past week). mysqld process is running automatically on startup. I am able to open and access "mysqldump" in the terminal window from shell. I am able to open and access "mysqlshow" in the terminal window from shell. I am able to open and access "mysqlbinlog" in the terminal window from shell. I am able to open and access "mysqlmanager" in the terminal window from shell. I am able to open and access "mysql" in the terminal window from shell. I find three "mysqladmin" files with zero bytes of content located at: 1) /root/(This is my root user home) 2) /usr/local/mysql/bin/ 3) /usr/local/mysql/ I find one "mysqladmin.1" file with 8k bytes of content located at: /usr/local/mysql/man/man1/ (If "mysqladmin.1" is the intended file instead of "mysqladmin" it will not open or run either) I have identified three files with the basic name "mysqlmanager". Is "mysqlmanager" intended to replace "mysqladmin" for the mac os X package download? I want to implement a stable and secure mysql data base on my local mac (production environment). The data base will later be deployed in an open internet environment. I appreciate your reply. Thank you. Hanon Sinay >How-To-Repeat: >Fix: >Submitter-Id: >Originator:System Administrator >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: :**-F1 mysqlbug (Fundamental)--L1--Top Auto-saving...done
Re: addressbook database
* Neil Watson > I'm thinking about an addressbook database. I would like an opinion on > my table layout (I still consider myself a mysql newbie): > > table user > uid > username > timestamp > > table secret > uid > secret > timestamp You don't mention keys? Why do you want to split the user table? A more 'normal' approach would be something like this: table user uid INT PRIMARY KEY username varchar(30) secret varchar(16) created datetime modified timestamp You probably want an UNIQUE index on username, presuming this is what the user is going to use to log in to the database. The same index is used both to ensure you don't insert duplicate users and to do fast lookups on the users when they log in. Note that I added an DATETIME column and gave a name to you TIMESTAMP column. Be aware of the difference between the data types DATETIME and TIMESTAMP: A TIMESTAMP column is changed automatically when any column in the row is changed, and it is set automatically to the current time when the row is created. http://www.mysql.com/doc/en/DATETIME.html > If you are going to use a web based front-end on your address book database, you may want a column in the user table for email address. A column for last_login could also be usefull. A 'hack' that could be usefull: add an pid to the user table. This pid points to a person-record for the user, i.e. with the users name, birthday, addresses and so on. The uid for that people record could be NULL if you don't want the user to be able to change his own address info (billing address?). Otherwise it is the uid for the user, and he can edit his own info in the same way as he edits any other person. Your application should probably prevent the user from deleting the person representing the user, even if you allow him to edit it. The user interface could also present the users personal info in a different way than other persons. > table people > pid > uid > firstname > lastname > birthday > timestamp The uid in the people table will prevent users from 'sharing' persons, but this may be just what you want. To be able to 'share' persons among users, you would have to remove uid from the person table, and use a separate table 'user_people' with the columns uid and pid. The pid column should be an integer, and it should be the primary key of the people table. The uid column should be of same type as the user.uid column, and it should have an index, alone or as the first part of a combined index. You should probably have an index on (uid,lastname) and (uid,firstname), but ... There is a weakness in this table design, the significance depends on the amount of persons you expect to handle for each user. The problem is that some persons have multiple first and/or last names. This prevents you from using ... WHERE ... LIKE '$criteria%' ... (Starting with the search criteria, ending with %). This is the syntax you need to use to utilize the index. If this is a 'normal' address book application, and you expect a few hundred persons per user, you should be okay, you can do scans through all people for the user with ... WHERE ... user.uid=people.uid AND people.firstname LIKE '%$firstname%' and people.lastname LIKE '%$lastname%' (note the leading %), and the index won't be used/needed. > table address > aid > pid > address > city > province > country > code > address_type > timestamp The aid column should be an integer primary key, and the pid column should have an index. Again, if the amount of data is big, you should normalize this table further. You should have separate tables for country, city and province. With a small amount of data this may be overkill, the same issues as for the people table applies. > table email > eid > pid > alias > string > email > timestamp The ied column should be an integer primary key, and the pid column should be indexed. I'm not sure what 'alias' and 'string' is supposed to store? Is it directly dependant on the email address? I guess 'alias' is to be used as a real name when sending email to this address? You could use CONCAT(people.firstname,' ',people.lastname) for that... or? I would expect the email table to contain only eid, pid, email and the timestamp column, if you need to know when the address was last modified or created. I would have expected a similar table for phone numbers, optionally related to address: table phone phid INT PRIMARY KEY pid aid phone varchar(12) You would as usual need an index on pid, and one on aid. The aid column must accept NULL, which means a phone number is related to the person directly, not to the address (for mobile/cell phones). If you normalize more and have a separate country table, this table could also store the country code, and your application could for instance automatically add the right country code when the user lists foreign phone numbers. In that case you would probably also need a country code in the user table, unless all your
Re: Local copy of database
> Finally, if anyone has or knows of a good phone directory already. > Please point me in the right direction. Have you considered LDAP? Run it over SSL with TLS - should calm any security concerns. Outlook (and most any other mail agent for that matter) can hook into it for address book lookups. You can store non-email related information in the LDAP database (address, phone number, etc.) The only catch is the offline-mode. I do not believe there is one but if you're considering installing MySQL on each machine you could set up a LDAP replicated slave instead. Don't get me wrong, writing a MySQL DB w/ GUI frontend would be more entertaining but this path is already well-traveled. Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
* Elisenda [...] > The explain select says as follows: [...] I re-formatted the query and the EXPLAIN output for readability: Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA, CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA table type key key_len ref rows FASE ref Participa 12 const,const 1157 CA ref Centro 7FASE.SQL_ID_CE 1 PP ref PP_ID 7FASE.PR_PP_ID_Coord 1 CE ref Centro 7CA.CA_ID_CE10 AU ref AU_AULA 256 FASE.AU_PR_Aula 264 (I removed the possible_keys and Extra columns) The first thing the EXPLAIN output tells us is in what order the server will access the tables. In this case the FASE table is read first, then the CA and PP tables are read based on the columns SQL_ID_CE and PR_PP_ID_Coord from FASE (the 'ref' column), then the CE table is read based on the value of CA.CA_ID_CE, and finally the AU table is read based on FASE.AU_PR_Aula. The 'rows' column hows approximately how many rows the server will have to read. It is a goal to make/keep these numbers low, I don't know if you did a EXPLAIN before you created you indexes, in that case you will see that the numbers in the 'rows' column was higher, possibly as high as the row count of the respective tables. A way to calculate how 'heavy' a select query is, is to multiply these numbers. In the case above, the multiplum is 1157*1*1*10*264 = 3054480. In other words, the server must examine approximately 3 million rows to produce your result. (Note that this is an estimate, based on statistics stored in the server. Running OPTIMIZE TABLE will update these statistics, and this may also change the servers preferred plan.) The 'ref' column for FASE says 'const,const'. This means the index used (Participa) is a combined index used to match two constants, presumably the SQL_ID_PY and PR_FLAG. Is the number 1157 close to correct? > What I know is that I have 753 records which match FASE.PR_flag=1 and > FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. I don't understand... How many FASE records with PR_flag=1 and SQL_ID_PY='P081'? You say when you join FASE and CA on those criteria you get 253 rows, but you should get 753? In general, if you get 'too few' rows on a multi-table join like the one you are doing here, it could be because some of the tables you join to does not have a corresponding row for the criteria. If that is the case, and you still want those rows to show up in the result, you can use LEFT JOIN for those tables. http://www.mysql.com/doc/en/JOIN.html > > Does it have to be with my query? Or does it have to be with data > in mysql, I mean I didn¹t insert them allright? I don't know. Check each table separately, use SELECT COUNT(*) FROM ... WHERE ... to check how many rows match any given criteria. Try to use the output of EXPLAIN SELECT to manually do what the server will be doing, and see if you get any unexpected results. Run OPTIMIZE TABLE to refresh the index statistics. http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html > For further speed improvements: Your AU_AULA and CA.Centro indexes could be replaced by combined indexes, (AU_AULA,AU_NIVEL) and (CA_ID_CE,CA_ID_IDIOMA_A) respectively. Your column CA.CA_ID_IDIOMA_A is defined as an integer, in your WHERE clause you should check for integer 6, not string '6'. This also applies to FASE.PR_FLAG. The AU.AU_NIVEL column was not mentioned in your previous table description, so I don't know if it's numeric or a string. If it is numeric, you should not use quotes on the constant. It will work, but the server must convert from string to integer, this take some time, using a constant of a type matching the column type is faster. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
addressbook database
I'm thinking about an addressbook database. I would like an opinion on my table layout (I still consider myself a mysql newbie): table user uid username timestamp table secret uid secret timestamp table people pid uid firstname lastname birthday timestamp table address aid pid address city province country code address_type timestamp table email eid pid alias string email timestamp Relationships: users (uid) can have many people (pid) in there contact list. people (pid) can have many addresses (aid). people (pid) can have many email addresses (eid). Thoughts? -- Neil Watson | Gentoo Linux Network Administrator | Uptime 12 days http://watson-wilson.ca | 2.4.22-ac1 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]
RE: Sorting question
You could try this: ORDER BY SUBSTRING_INDEX(TheColumn,'.',1), SUBSTRING_INDEX(SUBSTRING_INDEX(TheColumn,'.',-2),'.',1)+0, SUBSTRING_INDEX(TheColumn,'.',-1)+0 It is quite slow though, If possible, I'd suggest splitting that one column into 3 separate columns. You woudl save space (not decimal points to specify, and the nubmers would be stored as numbers) and sorting it the way you want would be much easier and faster. Chris -Original Message- From: Agrin, Nathan [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 10:44 AM To: [EMAIL PROTECTED] Subject: Sorting question I've seen this question posted in various forms on the web, but not in such a way that I have found helpful. I have a column of data in the following format name.xx.yy where the x's and y's represent numbers. When selected by mySQL, the column is sorted in an odd way, given by the following example: name.10.2 name.1058.5 name.205.3 Obviously, in our day to day thinking, the order should appear as: name.10.2 name.205.3 name.1058.5 Does anyone know a way to resort this data so that when it is displayed it is in common numerical order using a simple SQL statement? The data is being pulled from a mySQL database and being displayed in a php page. Thank you, Nate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Local copy of database
On Fri, Nov 14, 2003 at 03:18:49PM -0500, David M. Doggette wrote: I'm creating a database application for a phone directory for my church. People are very concerned about security and would like to run the application locally. Is it possible to distribute a local version of a How is having a copy of the database on each client more secure than having a single copy stored centrally where users will have to authenticate to gain access? -- Neil Watson | Gentoo Linux Network Administrator | Uptime 12 days http://watson-wilson.ca | 2.4.22-ac1 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]
Local copy of database
I'm creating a database application for a phone directory for my church. People are very concerned about security and would like to run the application locally. Is it possible to distribute a local version of a MySQL database without having to install MySQL on the local machine? Or is there a runtime environment that is easy for non-technical people to utilize? Finally, if anyone has or knows of a good phone directory already. Please point me in the right direction. -- David M. Doggette President 2HB Software Designs, Inc. 301.725.0299 v 301.725.0297 f 301.440.6087 m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting question
I've seen this question posted in various forms on the web, but not in such a way that I have found helpful. I have a column of data in the following format name.xx.yy where the x's and y's represent numbers. When selected by mySQL, the column is sorted in an odd way, given by the following example: name.10.2 name.1058.5 name.205.3 Obviously, in our day to day thinking, the order should appear as: name.10.2 name.205.3 name.1058.5 Does anyone know a way to resort this data so that when it is displayed it is in common numerical order using a simple SQL statement? The data is being pulled from a mySQL database and being displayed in a php page. Thank you, Nate
Using subpart indexes in order by/selects
Can anyone tell me how to structure a query to utilize a subparted index on Innodb? Haven't found any info on web search. Have an index (AGENT(4),SEARCH_NAME) that works on the select but breaks on the ORDER BY (using filesort). To get it to use the index I have to issue something like: select AGENT,SEARCH_NAME from PLMST where AGENT like '1001%' and SEARCH_NAME like 'SMITH%' This uses the index, but select AGENT,SEARCH_NAME from PLMST where AGENT like '1001%' and SEARCH_NAME like 'SMITH%' order by AGENT uses the index, but also uses filesort. Not really looking for help on this query, but some general information on how to construct queries to utilize index subparts/order by's. Any help greatly appreciated. Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Matt W wrote: I guess you'll have to see if it's fixed in the next release (4.0.17). Any clue as to when 4.0.17 will be released? Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting Colons in a Table
Can you post the error message you are getting? -Original Message- From: Quique Luna [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 4:33 PM To: [EMAIL PROTECTED] Subject: Inserting Colons in a Table Hi, I'm trying to load some data into a MySql table, but some of the srtings that I try to load come with colons in them and then I get an error... is it possible to insert colons and special characters into MySql? How? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Please help. MySQL Error.
do a show status like 'open_files' and a show variables like 'open_files_limit' -Original Message- From: William Bailey [mailto:[EMAIL PROTECTED] Sent: Friday, November 14, 2003 6:56 AM To: [EMAIL PROTECTED] Subject: Please help. MySQL Error. -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I am currently getting the following error on one of the mysql servers im looking after and wonder if anybody knows what specifically it relates to. Error in accept: Too many open files Im currently running MySQL version '4.0.14' under FreeBSD 5.1 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/tNDpzSfrYDJMXmERAvKBAKC6vY0PnowjAaI8sRIIu+Mumeum8gCfVWAH hRU4PeRdpbIGgWPI9/xWVJY= =wHd+ -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]
Re: subquery issue
Subquerys don't become available until 4.1 Version 3.X does not support them >From Mysql.com For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and and other methods. See section 6.4.2.11 Rewriting Subqueries for Earlier MySQL Versions. Also, if they worked, the second select would return 5 rows of data, and with an IN you would want a comma seperated list. but then again, htis method could work and i just may not be familiar with it. hth Jeff "Jeff Sawatzky" <[EMAIL PROTECTED]To: <[EMAIL PROTECTED]> om> cc: Subject: subquery issue 11/14/2003 10:41 AM I have a table called journal with the following fields ID = auto INT Date = DATE Order = INT Entry = TEXT The table stores journal entries and saves the date that the entry was entered on. The Order just ells me what order the entries were entered if there is more than one entry per date. I want to retrieve all the entries for that last five days with entries. Below is my query, but it doesn't work. SELECT *, DATE_FORMAT(journal.Date, '%W, %M %D, %Y') AS newDate FROM journal WHERE journal.Date IN (SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) ORDER BY journal.Date DESC, journal.Order DESC I get the following error: Query failed : You have an error in your SQL syntax near 'SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) O' at line 1 Does anyone know why this is happening? The version of mySQL I am using is 3.23. Jeff Sawatzky [EMAIL PROTECTED] 416-250-9111 x253 1-800-525-2568 Ontario Service Safety Alliance 4950 Yonge Street Suite 1500 Toronto, Ontario, Canada M2N 6K1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb-errors
Margrit, your disk or file system is probably broken: > InnoDB: Fatal error: cannot read from file. OS error number 17. 17 EEXIST File exists The error number does not make sense in a file read. Strange. You cannot remove ib_logfiles from an InnoDB installation. They are as important as ibdata files. You can look at http://www.innodb.com/ibman.html#Forcing_recovery about emergency recovery using a recent MySQL-4.0 version. Best regards, 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/ - Original Message - From: "Margrit Lottmann" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, November 14, 2003 3:27 PM Subject: innodb-errors > Following errors we've got by restarting mysqld > > 031114 10:26:51 mysqld started > InnoDB: Fatal error: cannot read from file. OS error number 17. > 031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1329 > InnoDB: We intentionally generate a memory trap. > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this binary > or one of the libraries it was linked against is corrupt, improperly built, > or misconfigured. This error can also be caused by malfunctioning hardware. > We will try our best to scrape up some info that will hopefully help diagnose > the problem, but since we have already crashed, something is definitely wrong > and this may fail. > > key_buffer_size=8388600 > read_buffer_size=131072 > max_used_connections=0 > max_connections=100 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > thd=0x83a9fb0 > Attempting 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, fp=0xbfffd058, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x80df7ea > 0x40035f05 > 0x82ac20c > 0x82acc41 > 0x825be18 > 0x820e66c > 0x816fb00 > 0x813d58a > 0x8138000 > 0x80e05f3 > 0x42017589 > 0x80a0da1 > New value of fp=(nil) failed sanity check, terminating stack trace! > Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved > stack trace is much more helpful in diagnosing the problem, so please do > resolve it > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort... > thd->query at (nil) is invalid pointer > thd->thread_id=1667594851 > The manual page at http://www.mysql.com/doc/en/Crashing.html contains > information that should help you find out what is causing the crash. > 031114 10:26:56 mysqld ended > > > > We have done recovery by using ibdata1 file from current update (by > removing logfiles > > ...mysqld startet ok ...working for recovery ...but stopped if any select >request to an InnoDB table was executed. > > Following trace-results we've got by using resove-tool... > > 0x80df7ea handle_segfault + 398 > 0x40035f05 _end + 935903133 > 0x81b5384 row_search_for_mysql + 5856 > 0x813f6e8 index_read__11ha_innobasePcPCcUi16ha_rkey_function + 644 > 0x8142c0f index_first__11ha_innobasePc + 35 > 0x8142d09 rnd_next__11ha_innobasePc + 41 > 0x8135704 rr_sequential__FP14st_read_record + 144 > 0x811311b join_init_read_record__FP13st_join_table + 75 > 0x810d346 sub_select__FP4JOINP13st_join_tableb + 78 > 0x810d101 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 393 > 0x81060bb mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP1 3select_result + 8683 > 0x81128cc handle_select__FP3THDP6st_lexP13select_result + 92 > 0x80ea8f2 mysql_execute_command__Fv + 978 > 0x80ee2bd mysql_parse__FP3THDPcUi + 349 > 0x80e9c68 dispatch_command__F19enum_server_commandP3THDPcUi + 1244 > 0x80ef85d do_command__FP3THD + 149 > 0x80e90e0 handle_one_connection + 584 > 0x40032faf _end + 935891015 > 0x420e790a _end + 970185122 > > How can we resolve our problems??? > > > -- > Mit freundlichen Gruessen > M.Lottmann > > Otto - von - Guericke Universitaet __ __ _ _ __ >Magdeburg / / / / / __ \__ // | / / > / / / / / /_/ / / / __ / |/ / >Margrit Lottmann/ /_/ / / _, _/ / /___// /| / >Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/ > Netze & Kommunikation > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- My
Re: rollback error
Fernando <[EMAIL PROTECTED]> wrote: > > In version 3.23.57 when i do a rollback i get this error message and the changes are > not undone, why? > > This is what i typed: (NOTE: 'insert.sql' insert a row correctly in an InnoDB table) > > mysql> SET AUTOCOMMIT=0; > Query OK, 0 rows affected (0.00 sec) > > mysql> BEGIN; > Query OK, 0 rows affected (0.00 sec) > > mysql> \. /home/fernando/scripts/insert.sql > Query OK, 1 row affected (0.00 sec) > > mysql> ROLLBACK; > ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back Provide output of the SHOW CREATE TABLE command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery issue
I have a table called journal with the following fields ID = auto INT Date = DATE Order = INT Entry = TEXT The table stores journal entries and saves the date that the entry was entered on. The Order just ells me what order the entries were entered if there is more than one entry per date. I want to retrieve all the entries for that last five days with entries. Below is my query, but it doesn't work. SELECT *, DATE_FORMAT(journal.Date, '%W, %M %D, %Y') AS newDate FROM journal WHERE journal.Date IN (SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) ORDER BY journal.Date DESC, journal.Order DESC I get the following error: Query failed : You have an error in your SQL syntax near 'SELECT DISTINCT journal.Date FROM journal ORDER BY journal.Date DESC LIMIT 5) O' at line 1 Does anyone know why this is happening? The version of mySQL I am using is 3.23. Jeff Sawatzky [EMAIL PROTECTED] 416-250-9111 x253 1-800-525-2568 Ontario Service Safety Alliance 4950 Yonge Street Suite 1500 Toronto, Ontario, Canada M2N 6K1
Re: bugs
Margrit Lottmann <[EMAIL PROTECTED]> wrote: > Why do you refuse to accept my messages. > I urgently need to send our bug report. Post bug reports to the bug databse: http://bugs.mysql.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bugs
Why do you refuse to accept my messages.
I urgently need to send our bug report.
--
Mit freundlichen Gruessen
M.Lottmann
Otto - von - Guericke Universitaet __ __ _ _ __
Magdeburg / / / / / __ \__ // | / /
/ / / / / /_/ / / / __ / |/ /
Margrit Lottmann/ /_/ / / _, _/ / /___// /| /
Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/
Netze & Kommunikation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2-way replication
Am Friday 14 November 2003 14:51 schrieb nm: > I set a 2-way replication with 2 servers > One is accessed and used for both select and updates. The other one is > backup > set to work through a failover software.. if the first server is down. > So the backup server can easily update the other server when it comes up > again. > Do you see any collision possibilities? Only one server is really used. I have been using 2-way replication on production machines for quite a while and never had any problems. In our case, all machines are used, primary key creation is done in the application and there will never be same pks used on different machines. HTH Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb-errors
Margrit, fix the problem that leads to OS error number 17. What does perror tell you about it? Regards, Frank. Margrit Lottmann schrieb: > > Following errors we've got by restarting mysqld > > 031114 10:26:51 mysqld started > InnoDB: Fatal error: cannot read from file. OS error number 17. > 031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1329 --cut > > How can we resolve our problems??? > > -- > Mit freundlichen Gruessen > M.Lottmann > > Otto - von - Guericke Universitaet __ __ _ _ __ >Magdeburg / / / / / __ \__ // | / / > / / / / / /_/ / / / __ / |/ / >Margrit Lottmann/ /_/ / / _, _/ / /___// /| / >Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/ > Netze & Kommunikation > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Showing multiple query results on multiple pages, help with this script!!!!
Hi, im using this script to show the query results, basically it shows
multiple results in multiple pages, when more than one result are found it
shows the first one and on the bottom of the pages it shows the number of
pages that remains, but fot some strange reason when you click on the first
or last page to show, it shows always the same query result . Can somebody
tell my what im doing wrong??
$db = mysql_connect("localhost", "root");
mysql_select_db("qllamo",$db);
$result = mysql_query("SELECT * FROM llamadas",$db);
$pagelimit = "1";
$totalrows = mysql_num_rows($result);
$pagenums = ceil ($totalrows/$pagelimit);
if ($page==''){
$page='1';
}
// create a start value
$start = ($page-1) * $pagelimit;
// blank matches found
echo "" . $totalrows . " Llamadas encontradas\n";
// Showing Results 1 to 1
$starting_no = $start + 1;
if ($totalrows - $start < $pagelimit) {
$end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
$end_count = $start + $pagelimit;
}
if ($totalrows - $end_count > $pagelimit) {
$var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
$var2 = $totalrows - $end_count;
}
$space = " ";
printf("Id: %s\n", mysql_result($result,0,"id"));
printf("Destinatario: %s\n", mysql_result($result,0,"destinatario"));
printf("Fecha: %s\n", mysql_result($result,0,"fecha"));
printf("Hora: %s\n", mysql_result($result,0,"hora"));
printf("Apellido: %s\n", mysql_result($result,0,"apellido"));
printf("Nombre: %s\n", mysql_result($result,0,"nombre"));
printf("Teléfono: %s\n", mysql_result($result,0,"telefono"));
printf("Mail: %s\n", mysql_result($result,0,"mail"));
printf("Mensaje: %s\n", mysql_result($result,0,"mensaje"));
printf("Receptor: %s\n", mysql_result($result,0,"receptor"));
// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
echo "? Previous"
. $space . $pagelimit . "" . $space . $space . "";
}
// dynamic page number links (make sure to change yourpage.php to the name
of your page)
for ($i=1; $i<=$pagenums; $i++) {
if ($i!=$page) {
echo " $i";
}
else {
echo " $i";
}
}
// next link (make sure to change yourpage.php to the name of your page)
if ($page<$pagenums) {
echo "" . $space . $space . $space . $space . " Next " . $var2 . " ?";
}
?>
Thanx.
Emilio.
_
MSN Shopping upgraded for the holidays! Snappier product search...
http://shopping.msn.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running without logfiles
I am using Innodb Tables but I Like to run this database without creating any logfiles. Is there a way that Mysql doesn't create any logfiles at all . I can recover from a master system so this database can or is allowed to loose the data
Showing multiple results in multiple pages, this scripts doesnt work!
Hi, im using this script to show the query results, basically it shows multiple results in multiple pages, when more than one result are found it shows the first one and on the bottom of the pages it shows the number of pages that remains, but fot some strange reason when you click on the first or last page to show, it shows always the same query result . Can somebody tell my what im doing wrong?? Thanx. Emilio. _ Great deals on high-speed Internet access as low as $26.95. https://broadband.msn.com (Prices may vary by service area.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlfront versus mysqlcc
Try SQLyog at www.sqlyog.com It is pretty good, and full of interesting features. Bye > I currently use the excellent mysqlfront which is sadly no longer > supported. > > I've tried mysqlcc but it seems non-intuitive, and missing loads of > features. It also has a problem working with old versions of mysql > 3.23<.47 > > Or have I missed something, does anyone recommend mysqlcc??? > > > zzapper > -- > > > vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal > ggVGg?" > > > http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2-way replication
I set a 2-way replication with 2 servers One is accessed and used for both select and updates. The other one is backup set to work through a failover software.. if the first server is down. So the backup server can easily update the other server when it comes up again. Do you see any collision possibilities? Only one server is really used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb-errors
Following errors we've got by restarting mysqld
031114 10:26:51 mysqld started
InnoDB: Fatal error: cannot read from file. OS error number 17.
031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1329
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x83a9fb0
Attempting 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, fp=0xbfffd058, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80df7ea
0x40035f05
0x82ac20c
0x82acc41
0x825be18
0x820e66c
0x816fb00
0x813d58a
0x8138000
0x80e05f3
0x42017589
0x80a0da1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil) is invalid pointer
thd->thread_id=1667594851
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
031114 10:26:56 mysqld ended
We have done recovery by using ibdata1 file from current update (by
removing logfiles
...mysqld startet ok ...working for recovery ...but stopped if any select
request to an InnoDB table was executed.
Following trace-results we've got by using resove-tool...
0x80df7ea handle_segfault + 398
0x40035f05 _end + 935903133
0x81b5384 row_search_for_mysql + 5856
0x813f6e8 index_read__11ha_innobasePcPCcUi16ha_rkey_function + 644
0x8142c0f index_first__11ha_innobasePc + 35
0x8142d09 rnd_next__11ha_innobasePc + 41
0x8135704 rr_sequential__FP14st_read_record + 144
0x811311b join_init_read_record__FP13st_join_table + 75
0x810d346 sub_select__FP4JOINP13st_join_tableb + 78
0x810d101 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 393
0x81060bb
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 8683
0x81128cc handle_select__FP3THDP6st_lexP13select_result + 92
0x80ea8f2 mysql_execute_command__Fv + 978
0x80ee2bd mysql_parse__FP3THDPcUi + 349
0x80e9c68 dispatch_command__F19enum_server_commandP3THDPcUi + 1244
0x80ef85d do_command__FP3THD + 149
0x80e90e0 handle_one_connection + 584
0x40032faf _end + 935891015
0x420e790a _end + 970185122
How can we resolve our problems???
--
Mit freundlichen Gruessen
M.Lottmann
Otto - von - Guericke Universitaet __ __ _ _ __
Magdeburg / / / / / __ \__ // | / /
/ / / / / /_/ / / / __ / |/ /
Margrit Lottmann/ /_/ / / _, _/ / /___// /| /
Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/
Netze & Kommunikation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb-errors
Following errors we've got by restarting mysqld
031114 10:26:51 mysqld started
InnoDB: Fatal error: cannot read from file. OS error number 17.
031114 10:26:56 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1329
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x83a9fb0
Attempting 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, fp=0xbfffd058, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80df7ea
0x40035f05
0x82ac20c
0x82acc41
0x825be18
0x820e66c
0x816fb00
0x813d58a
0x8138000
0x80e05f3
0x42017589
0x80a0da1
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil) is invalid pointer
thd->thread_id=1667594851
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
031114 10:26:56 mysqld ended
We have done recovery by using ibdata1 file from current update (by
removing logfiles
...mysqld startet ok ...working for recovery ...but stopped if any select
request to an InnoDB table was executed.
Following trace-results we've got by using resove-tool...
0x80df7ea handle_segfault + 398
0x40035f05 _end + 935903133
0x81b5384 row_search_for_mysql + 5856
0x813f6e8 index_read__11ha_innobasePcPCcUi16ha_rkey_function + 644
0x8142c0f index_first__11ha_innobasePc + 35
0x8142d09 rnd_next__11ha_innobasePc + 41
0x8135704 rr_sequential__FP14st_read_record + 144
0x811311b join_init_read_record__FP13st_join_table + 75
0x810d346 sub_select__FP4JOINP13st_join_tableb + 78
0x810d101 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 393
0x81060bb
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 8683
0x81128cc handle_select__FP3THDP6st_lexP13select_result + 92
0x80ea8f2 mysql_execute_command__Fv + 978
0x80ee2bd mysql_parse__FP3THDPcUi + 349
0x80e9c68 dispatch_command__F19enum_server_commandP3THDPcUi + 1244
0x80ef85d do_command__FP3THD + 149
0x80e90e0 handle_one_connection + 584
0x40032faf _end + 935891015
0x420e790a _end + 970185122
How can we resolve our problems???
--
Mit freundlichen Gruessen
M.Lottmann
Otto - von - Guericke Universitaet __ __ _ _ __
Magdeburg / / / / / __ \__ // | / /
/ / / / / /_/ / / / __ / |/ /
Margrit Lottmann/ /_/ / / _, _/ / /___// /| /
Universitaetsrechenzentrum \/ /_/ |_| // /_/ |_/
Netze & Kommunikation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help. MySQL Error.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I am currently getting the following error on one of the mysql servers im looking after and wonder if anybody knows what specifically it relates to. Error in accept: Too many open files Im currently running MySQL version '4.0.14' under FreeBSD 5.1 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/tNDpzSfrYDJMXmERAvKBAKC6vY0PnowjAaI8sRIIu+Mumeum8gCfVWAH hRU4PeRdpbIGgWPI9/xWVJY= =wHd+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
Roger, Your help was fantastic. It seems that it goes better. At the end I achieve some result but not all I need. The explain select says as follows: EXPLAIN Select FASE.PR_Date_Visita_2, CE.CE_Centro, CE.CE_Domicilio, CE.CE_CP, CE.CE_Poblacion, CE.CE_Capital, CE.CE_PROV, CE.CE_CCAA,CA.CA_Horario, PP.PP_Contacto, PP.PP_Cargo, AU.AU_A_M, AU.AU_A_F >From FASE,CE,CA,PP,AU where FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG='1' AND CA.CA_ID_IDIOMA_A='6' AND AU.AU_NIVEL='13.14' AND FASE.SQL_ID_CE=CE.CE_ID_CE AND FASE.SQL_ID_CE=CA.CA_ID_CE AND CE.CE_ID_CE=CA.CA_ID_CE AND FASE.PR_PP_ID_COORD=PP.PP_ID_PP AND FASE.AU_PR_AULA=AU.AU_AULA\ table type possible_keys keykey_len ref rows Extra FASE ref Proyecto,Folleto,Solicitud,Participa,Seguimiento,Ganador,Solicitud_CCAA,Soli citud_PROV,TipoSL_CCAA,TipoSL_PROV,SG_Recibibido_CCAA,SG_Recibibido_PROV,PR_ Aula,SL_Categoria_CCAA,Centro Participa 12 const,const 1157 Using where CA ref Centro,IDIOMA_A Centro 7FASE.SQL_ID_CE 1 Using where PP ref PP_ID PP_ID 7FASE.PR_PP_ID_Coord 1 Using where CE ref Centro Centro 7CA.CA_ID_CE 10Using where AU ref Nivel_FASE,AU_AULA,Au_Nivel AU_AULA256 FASE.AU_PR_Aula 264 Using where What I know is that I have 753 records which match FASE.PR_flag=1 and FASE.SQL_ID_PY=P081 and CA.CA_ID_Idioma_A=6 and my result is 253 records. Does it have to be with my query? Or does it have to be with data in mysql, I mean I didn¹t insert them allright? eli > * Elisenda >> The problem is that it 's too slow and the result doesn't appear. >> I am going to try to explain the query. > > Please do that using the EXPLAIN SELECT statement: > > http://www.mysql.com/doc/en/EXPLAIN.html > > > This will show what index is beeing used on the different joins, and > approximately how many rows the server must handle to produce your result. I > suspect that in this case there are no index on some of the columns beeing > used for the joins, whih means the server must scan the entire table > multiple times. This will often result in a query that appears to 'hang', no > result is returned. The server is actually working on the result, but it > will take 'forever', you will normally kill your connection before you > recieve anything. > > More below... > >> Fields from Table FASE: (300.000 records) >> >> ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, >> SQL_ID_PY char(6), >> SQL_ID_CE char(6), >> PR_flag INT, >> PR_Date_Visita_2 Date, >> AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) >> >> Field from Table CE (30.000 records) >> >> CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, >> CE_ID_CE char(6), >> CE_Centro varchar(32), >> CE_Domicilio varchar(32), >> CE_CP varchar(5), >> CE_Poblacion varchar(30), >> CE_ID_Capital char(2), >> CE_Capital varchar(30), >> CE_ID_PROV char(2), >> CE_PROV varchar(15), >> CE_ID_CCAA char(2), >> CE_CCAA varchar(15) >> >> Field from Table CA (30.000 records) >> >> CA_ID INT NOT NULL PRIMARY KEY, >> CA_ID_User char(6), >> CA_ID_CE char(6), >> CA_Centro varchar(32), >> CA_ID_Idioma_A INT, >> CA_Horario varchar(30) >> >> Fields from table AU (700.000 records) >> >> AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, >> AU_ID_CE char(6), >> AU_ID_PY char(6), >> AU_ID_FASE INT, >> AU_A_M INT, >> AU_A_F INT, >> AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) >> >> Fields from table PP (200.000 records) >> >> PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, >> PP_ID_PP char(6), >> PP_ID_CE char(6), >> PP_Contacto char(50), >> PP_ID_Cargo char(6), >> PP_Cargo char(32) > > There seems to be only primary keys on these tables? No other index defined? > If that is the case, this is probably the reason of your problem. Put an > index on any column used to join other tables, the so-called foreign keys. > >> I select from Fase some records. From fase I only want records (SQL_ID_CE) >> that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this > selection, > > You can create a combined index on SQL_ID_PY and PR_FLAG: > > CREATE INDEX SQL_ID_PY_PR_FLAG_INDEX ON FASE (SQL_ID_PY,PR_FLAG) > >> I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have >> CA.CA_ID_IDIOMA_A= '6'. > > Then probably both AU.AU_NIVEL and CA.CA_ID_IDIOMA_A should be indexed. > >> In WHERE I write >> >> AU.AU_Aula= fase.AU_PR_Aula AND >> AU.AU_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = Fase.SQL_ID_CE AND >> CE.CE_ID_CE = PP.PP_ID_CE AND >> Fase.PR_PP_ID_Coord = PP.PP_ID_PP >> >> >> Main relation in all tables is SQL_ID_CE. > > Then all columns related to SQL_ID_CE should have an index. Probably also > some of the other fields mentioned above: AU.AU_Aula, fase.AU_PR_Aula, > AU.AU_ID_CE, CA.CA_ID_CE, CE
unicode support
Quick question I haven't found in the docs. I have a column with unicode (utf8 or ucs2) character set. How can I use a national collation on it? (upper(), lower(), sort order) None of the examples in the docs work... :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlfront versus mysqlcc
I currently use the excellent mysqlfront which is sadly no longer supported. I've tried mysqlcc but it seems non-intuitive, and missing loads of features. It also has a problem working with old versions of mysql 3.23<.47 Or have I missed something, does anyone recommend mysqlcc??? zzapper -- vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?" http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query not visible by sh processlist
Sohail Hasan <[EMAIL PROTECTED]> wrote: > > Is there a possibility that a certain query that is executing in mysql > by application is not visible by a "show processlist" command. As > happened in my database a query was taking number of CPU cycles but when > seen through the show processlist command nothing was displayed. > If the user dosn't have SUPER privilege he can see only his own threads. -- 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: Query with temporary table
"Jeff McKeon" <[EMAIL PROTECTED]> wrote: > Is is possible to do a select query with a left join from a real table > to a temporary table? Yes. > I'm trying it but keep getting "unkown table > 'tablename' in field list" error. Show us you query. Don't forget that temporary table is visible only for the current connection. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysql.dll
Hi all, I would like to know who develops and how to obtain libmysql.dll, and is there any Object Pascal header file for this library, or I have to make my own? Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query not visible by sh processlist
Hi All, Is there a possibility that a certain query that is executing in mysql by application is not visible by a "show processlist" command. As happened in my database a query was taking number of CPU cycles but when seen through the show processlist command nothing was displayed. shasan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert select query problem
Luis Lebron <[EMAIL PROTECTED]> wrote:
>
> I am try to copy a couple of fields from one table to another. Here is my
> query
>
> Insert into users_test (firstName, lastName) Select firstName, lastName from
> users;
>
> The users table has 1,263 rows. However, only 1 row is inserted into
> users_test.
>
> If I perform the following query
>
> Insert into users_test Select * users;
>
> all the rows are inserted. What am I doing wrong in the first query?
>
Worked perfect for me:
mysql> create table i1(
-> firstname varchar(10),
-> lastname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> create table i2(
-> firstname varchar(10),
-> lastname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into i1 values('aaa','aaa'),('bbb','bbb'),('ccc','ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into i2(firstname, lastname) select firstname, lastname from i1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from i2;
+---+--+
| firstname | lastname |
+---+--+
| aaa | aaa |
| bbb | bbb |
| ccc | ccc |
+---+--+
3 rows in set (0.00 sec)
mysql> truncate i2;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into i2 select * from i1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from i2;
+---+--+
| firstname | lastname |
+---+--+
| aaa | aaa |
| bbb | bbb |
| ccc | ccc |
+---+--+
3 rows in set (0.00 sec)
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
We used to have a fair amount of data in InnoDB. Had a few crashes from power failures at a crappy hosting provider. Everytime the InnoDB engine seemed to detect the crashes and read up its logfiles and recover. I've never tried any "manual" InnoDB recovery / data extraction. Are there methods? so far it seems to me that InnoDB manages itself after a crash!? innodb.com: "InnoDB tables have automatic crash recovery. You do not need to repair your tables if the operating system or the database server crashes, when there is no disk image corruption" Mike On Thursday 13 November 2003 17.29, Nils Valentin wrote: > Hi there, > > I am not sure I understood your question correctly. What exactly is it that > you want to test ? > > a) the recovery possibility in case of power down > b) the recovery possibility in case of client disconnection (network > interruption, timeout etc.) > c) Recovery possibilities in general accessing the data files directly and > indirectly using command line and/or GUI tools. > > In case a) mysql would recover the data files itself in most cases next > time when the server is started as long as the logfiles, datafiles, config > files are all there in the original positions (talking about InnoDB). > > b) If the network connection times out or client is diconnected than all > executed commands since the last commit,begin gets rolled back (will not be > applied) > > c) If the Innodb files are damaged so that the mysql server does not > startup than no client tool (command line or GUI) that uses the indirect > access method can access any data. > > I know that there are tools in the mysql package which access and repair > (My)ISAM tables directly (server doesnt need to run), but that isnt true > for the InnoDB tables I believe. I am not sure if InnoDB Hotbackup can > directly access the InnoDB tables. > > What I know is that there will be a book coming up in February from Paul > Dubois (Certification Study Guide) which is already described on Amazon. > > When it comes out I believe that it will be the best book on the market so > far, answering many of those and similar questions. I had the honour to > review one of those preprints, all I can say so far is "Very impressive", > you will see for yourself.. > > On Friday 14 November 2003 00:58, nm wrote: > > Do you know how to test a crash and a rollback? > > -- > kind regards > > Nils Valentin > Tokyo/Japan > > http://www.be-known-online.com/mysql/ -- Ongame E-Solutions AB Mikael Fridh Junior Systems Administrator Smedsgränd 3, 753 20 Uppsala, Sweden Mobile: +46 708 17 42 00 Office: +46 18 69 55 00 Fax: +46 18 69 44 11 e-mail: [EMAIL PROTECTED] http://www.ongame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF's and MySQL with-ldflags -all-static
Hi, everyone, As said in documentation, there is no need to build MySQL with -rdynamic: "If you want to use an UDF that needs to access symbols from mysqld (like the methaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). " I need a simple udf, to convert string from one format to another like "09:32:52.370 UTC Wed Nov 12 2003" into "2003-11-12 09:32:52". To let it insert into date field. Can I write such udf without using any "specific" symbols, that could compile in MySQL -with-mysql-ldflags=-all-static? I've written just simple udf without using anything and it realy does nothing (it is just function declarations and empty dunction bodies). I compiled it succesfully, and put "udf_test.so" into /usr/lib. But when I tried load it into MySQL using: > CREATE myudftest RETURNS STRING SONAME "udf_test.so"; the result was error 2013 - Lost connection to MySQL server during query. Can anyone help me in this situation? I dont want to recompile MySQL with -rdynamic because I need only one simple udf, but I need it. Ho to make this with staticaly compiled MySQL? Has anyone already made this? Thanks, Best regards to all, Arunas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_client
Alaios <[EMAIL PROTECTED]> wrote: > Hi i have just installed the mysql_client > and i am trying to remotely connect to the database of > the other pc so i gave the following command > mysqlshow -h 192.168.64.12 -u [EMAIL PROTECTED] > mysqlshow: Host 'akroneiro' is not allowed to connect > to this M This error is described at: http://www.mysql.com/doc/en/Access_denied.html > > What i msut do in order to gain access to the db? Hwo > i can create users ? Is there any easy way for that? You should add entry to the 'user' table using GRANT statement: http://www.mysql.com/doc/en/GRANT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Built in SSL?
Hi, 1.First generate un encrypted certificates. This can be done by using an Open SSL library. The instructions are available at http://www.mysql.com/doc/en/Secure_Create_Certs.html Note: Here un encrypted certificates means certificates that are not protected by any password. When MySQL code tries to read these certificates it can straight away read those certificates. Otherwise the code has to provide some way to get the password from the user and then decrypt the certificates. This is not yet implemented in MySQL. So any certificate that is encrypted with a password cannot be used with an SSL session on MySQL. 2.Copy the certificates to a folder on your server and client machines. Eg: /mysql/SSL You can also use the sample certificates provided by MySQL from MySQL repository. 3.Add the following entries in your my.cnf in such a way that the server reads these certificates from the specified location. [mysqld] ssl-ca=your_drive:\mysql\SSL\cacert.pem ssl-cert=your_drive:\mysql\SSL\server-cert.pem ssl-key=your_drive:\mysql\SSL\server-key.pem 4.Add the following entries in your my.cnf/my.ini(on windows) in such a way that the client reads these certificates from the specified location. [mysql] ssl-ca=your_drive:\mysql\SSL\cacert.pem ssl-cert=your_drive:\mysql\SSL\client-cert.pem ssl-key=your_drive:\mysql\SSL\client-key.pem 4.Start MySQL server. 5.Start MySQL client (mysql monitor) on your client machine. 6.On mySQL monitor type the following mysql> status It should display some thing like this. SSL : Cipher in use is EDH-RSA-DES-CBC3-RSA It indicates that the mysql client (mysql monitor) is using an SSL channel to communicate with MySQL server. Trouble shooting: The MySQL server comes up irrespective of ant failures in the SSL initialization. But it cannot service SSL connections. It will service non-service connections only. A MySQL server at one time can server both SSL and non SSL connections. But the a mysql client cannot connect to a MySQL server with SSL options when the server is not accepting the SSL connections. It will throw up an error. Unfortunately the error reporting with SSL on MySQL is very poor. But most of the cases the SSL connection may fail only because of a bad or expired certificates. In all other cases the connection should succeed. You need not use stunnel or ssh tunneling for this. >>> "Paul Fine" <[EMAIL PROTECTED]> 11/13/2003 2:01:39 AM >>> Would anyone be kind enough to provide an example of using MySQL 4 with it's apparent built in SSL functionality to connect from a Win client to *Nix box? Am I correct in assuming that this new functionality means that I will not have to use stunnel or ssh tunneling? Thanks for any info! -- 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]
