RE: inserting null to not null columns
Hi Donna, You have to include the column `type` in the SQL-statement, otherwise the not specified column will have its indirect null translated to an empty string or 0 (for strings/numeric). A bug or a feature? Actually I've used it as a feature sometimes when using MySQL to move large amount of dirty data between different systems. Be careful when not including NOT NULL columns in your inserts... To get around the problem and get more background info, read: http://dev.mysql.com/doc/mysql/en/constraint_NOT_NULL.html Regards, Thomas Lundström, Ongame E-Solutions AB -Original Message- From: Donna Hinshaw [mailto:[EMAIL PROTECTED] Sent: den 21 september 2004 21:07 To: [EMAIL PROTECTED] Subject: inserting null to not null columns Hi folks: I have an InnoDB database, the tables created using MySQL Control Center 0.9.4-beta (winXP pro platform). Each table has some columns which I have checked as Nulls Allowed. I am building a pure Java GUI to the database. Got the SQL statements working fine, but have discovered that I can successfully insert rows into a table without including a value for a column which should be blocking nulls. e.g. Table A id (PK, auto increment) name type ssn ( nulls allowed specified) == name and type do not have nulls allowed specified, so I think they should be NOT NULL. they also have no default specified. then insert into A (id,name,ssn) values (NULL,Jane,9) this statement works fine, but I think it should give me an error by saying that I'm trying to insert a row without providing a value for the type column (which has no default specified). Looking at the create statement for the tables, MySQL Control Center has supplied defaults of blanks...can I turn off that preference ? using MySQL 4.0.18 Can anyone provide clarification? thanks... Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable top drop table, error 1051
Hi, I've a problem that I can't drop a certain table, always get back the error unknown table. Version: 4.0.21 (Debian Testing) Table-Type: InnoDB mysql show tables; [...] | produkt_kategorie| mysql drop table produkt_kategorie; ERROR 1051: Unknown table 'produkt_kategorie' The ownerships/permission seem right to me too: -rw-rw 1 mysql mysql 8670 Sep 21 11:23 produkt_kategorie.frm mysql describe produkt_kategorie; | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | pk_id| int(10) unsigned | | PRI | NULL| auto_increment | | pk_pt_id_typ | int(10) unsigned | | MUL | 0 || | pk_kategorie | varchar(255) | YES | | NULL|| | pk_sortid| int(10) unsigned | YES | | NULL|| pk_pt_id_typ is from a 1:n relation to the table produktkategorie_typ. I do the operation as user 'root' which has all access-rights. When I view the table in phpmyadmin (using 2.5.7pl1) I also see this additional information: InnoDB free: 44032 kB; (`pk_pt_id_typ`) REFER `produktkategorie_typ`(`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION I've used DbDesigner4 to design the table and then use the synchronisation feature to create the tables in the database. I'm using a 1:n relation from another table, produktkategorie_typ, to this table. I've created the Reference Definitions, but yet haven't assigned any actions (thus there's NO ACTION defined as seen above). I believe it has to do with the relation/reference definitions from InnoDB, so to me the message seems missleading. The only way for me to drop the table is to completely drop the database and recreate. thanks for any hints - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing the correct my.cnf
Hello, I have two installations of mysql on my computer a stable 4.x version and the aplha 5.x version. I want to use a separate my.cnf file for my 5.x version. But as soon as I start the mysqld_safe it tries to read /etc/my.cnf which belongs to my 4.x server. This ofcourse is not what I want. I have placed the my.cnf in the root directory of my mysql5 directory (/usr/local/mysql5/my.cnf) but I dont know how to force mysqld_safe to read that file and not the one in /etc/my.cnf. Could someone give my a solution on how to fix this? Thanks in advance /Jonas --- Jonas Ladenfors Software engineer Neuronova AB Fiskartorps vägen 15 Stockholm +46 8 786 09 26 +46 73 624 33 89 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with normalized table
Johan Pettersson wrote: Hi Philippe, how about this: SELECTstrategyid, COUNT(marketid) cnt FROMstrategies_markets WHERE marketid in(selected markets here) GROUP BY strategyid ORDER BY cnt DESC; The resultset should contain the best matches, in descending order. To find strategies that contains all markets (but that may contain others) you could use: SELECTstrategyid, COUNT(marketid) cnt FROMstrategies_markets WHERE marketid in(selected markets here) GROUP BY strategyid HAVING cnt = number of selected markets If you reverse the key-order in the primary key of strategies_markets, this baby should really fly. To enable the foreign key, I had to add an INDEX on this. The query works fine, but I can't get only one strategy with it. I'll go for the first solution, I hope it won't lag with 8-leg strategy on a wide table... -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.5 has been released
Hi Lenz I could not download it. When I click on DOWNLOAD link, it takes me to some strange website. Prafulla -Original Message- From: Lenz Grimmer [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 10:37 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: MySQL 4.1.5 has been released -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.1.5, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the second 4.1 gamma release, mainly fixing recently discovered bugs in preparation for the upcoming production release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * The Windows Installer packages are now making use of the Windows Installer (MSI) and include a new MySQL Server Instance Configuration Wizard that can be used to easily generate an optimized server configuration and to apply core security settings. * InnoDB: Added configuration option innodb_autoextend_increment for setting the size in megabytes by which InnoDB tablespaces are extended when they become full. The default value is 8, corresponding to the fixed increment of 8MB in previous versions of MySQL. Bugs fixed: * Fixed name resolving of external fields of subqueries if subquery placed in select list of query with grouping. (Bug #5326) * Fixed detection of using same table for updating and selecting in multi-update queries. (Bug #5455) * The values of the max_sort_length, sql_mode, and group_concat_max_len system variables now are stored in the query cache with other query information to avoid returning an incorrect result from the query cache. (Bug #5394) (Bug #5515) * Fixed syntax analyzer with sql_mode=IGNORE_SPACE. It happened to take phrases like default .07 as identifier.identifier. (Bug #5318) * Fixed illegal internal field length of user variables of integer type. This showed up when creating a table as select @variable. (Bug #4788) * Fixed a buffer overflow in prepared statements API (libmysqlclient) when a statement containing thousands of placeholders was executed. (Bug #5194) * Fixed a bug in the server when after reaching a certain limit of prepared statements per connection (97), statement ids began to overlap, so occasionally wrong statements were chosen for execution. (Bug #5399) * Fixed a bug in prepared statements when LIKE used with arguments in different character sets crashed server on first execute. (Bug #4368) * Fixed a bug in prepared statements when providing '-00-00' date to a parameter lead to server crash. (Bug #4231, Bug #4562) * Fixed a bug in OPTIMIZE TABLE that could cause table corruption on FULLTEXT indexes. (Bug #5327) * InnoDB: Fixed a bug that InnoDB only allowed a maximum of 1000 connections inside InnoDB at the same time. A higher number could cause an assertion failure in sync0arr.c, line 384. Now we allow 1000, 1, or 5, depending on the buffer pool size. (Bug #5414) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFBUF/MSVDhKrJykfIRAutQAJ4kOnOfH+zFPrcOccPqQrzyafMP8ACfXbu7 9sHpaM3kvFUv3MxBmYXWe8U= =QJe/ -END PGP SIGNATURE- -- MySQL Announce Mailing List For list archives: http://lists.mysql.com/announce 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: Custom Auto-Increment Problem
In article [EMAIL PROTECTED], Dan Tappin [EMAIL PROTECTED] writes: Hi Shawn, First off thanks for the tip. I had read that page once already but after reading twice again after your post I realized that the answer was right there. Wrapping that concept around my brain really hurt but I get it now. I had this: CREATE TABLE projects ( id int auto_increment, id_project int, id _client, PRIMARY KEY (id) ) When I should have this: CREATE TABLE projects ( id int auto_increment, id_client int, PRIMARY KEY (id_client, id) ) I would not do that since it's absolutely nonstandard and works only with MyISAM tables. Apparently id_clientkey is the number of records with the same id_client and smaller ids. This can be easily calculated on the fly and thus should not be stored in the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Having rows or fields that can't be modified once entered.
I was wondering if there was some builtin mechanism I could use to ensure that a field or row in my database could not be altered after it was initially inserted. Is there anything in the database that would allow this type of functionality? Thanks!! -- Jesse W. Asher CISSP, CISM, SCSA, SCNA
Re: Secure logon from VB.net
On 21 Sep 2004 at 10:14, Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
Hi Tom,
There is nothing you can do to encyrpt the password *within* you program, a
determined hacker will be able to decompile your program and get the username /
password if they have access to it.
If the potential hackers are on your network and can sniff traffic between your client
and the server, then you need to use some sort of SSL connection.
Seeing as you are using vb.net I am assuming your program runs on windows.
So far I have not seen a SSL aware windows MySQL client. You can compile one
yourself, but this doesn't seem to be an easy route.
The best option I can think of is to install stunnel ( http://www.stunnel.org ) on the
server and the client systems. ( It is available for both unix/linux and windows ).
This will SSL encyrpt all traffic on the designated ports between the client and
server, you can also set it to only accept connections with the right client
certificates.
I have succesfully used this setup in production systems.
You will have to modify your MySQL user settings so that the host is *localhost*.
Hope this helps
Regards
Ian
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having rows or fields that can't be modified once entered.
- Original Message - From: Jesse W. Asher [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 6:34 AM Subject: Having rows or fields that can't be modified once entered. I was wondering if there was some builtin mechanism I could use to ensure that a field or row in my database could not be altered after it was initially inserted. Is there anything in the database that would allow this type of functionality? Thanks!! Have a look at the GRANT statement at http://dev.mysql.com/doc/mysql/en/GRANT.html. As long as no one is given any Insert, Update, or Delete privilege on the table, your table should be safe against changes. Important note: The 'ALL' privilege will give the Insert, Update, and Delete privileges - and others - to your users so be sure you haven't granted Insert, Update, or Delete explicitly to anyone at either the table or column level, and be sure you haven't granted All to anyone explicitly and you should achieve the desired effect. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Often see threads in the end state taking too much time
Hello We're having some locking problems with our vBulletin 3.0 forum. Snapshots of mysql process list show that some simple queries linger too much in the end state, locking subsequent updates: Id UserHostdb Command TimeState Info .. 33753 rcgroups12.34.56.78:56520 forums Query 7 end UPDATE session\n\t\tSET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)', lastactivity = 1095853377, styleid = 0, bypass = 0\n\t\t ,inforum = 129, inthread = 151687, incalendar = 0, badlocation = 0\n\t\t WHERE sessionhash = 'b71a2c3f73f507e4cd548efedb836887' . session table is a simple HEAP table which usually holds 2500-3500 records at the peak time: CREATE TABLE `session` `sessionhash` varchar(32) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `host` varchar(15) NOT NULL default '', `useragent` varchar(100) NOT NULL default '', `lastactivity` int(10) unsigned NOT NULL default '0', `location` varchar(255) NOT NULL default '', `styleid` smallint(5) unsigned NOT NULL default '0', `althash` varchar(32) NOT NULL default '', `badlocation` smallint(5) unsigned NOT NULL default '0', `inforum` smallint(5) unsigned NOT NULL default '0', `inthread` int(10) unsigned NOT NULL default '0', `incalendar` int(10) unsigned NOT NULL default '0', `loggedin` smallint(5) unsigned NOT NULL default '0', `idhash` varchar(32) NOT NULL default '', `bypass` smallint(6) NOT NULL default '0', PRIMARY KEY (`sessionhash`), KEY `userid` (`userid`), KEY `location` (`location`), KEY `lastactivity` (`lastactivity`) ) TYPE=HEAP IMHO 7 seconds is a bit too much for a simple UPDATE on primary key. DB server (dual 2.4GHz Xeon Redhat9 box with 2G of RAM) load average is rarely going over 0.8. Swapping is minimal. Disk IO activity is well within acceptable bounds, with peak rate of 1Mb/sec. Mysql is 4.0.21 installed from official mysql.com's RPMs. Any other ideas what's going on here? Documentation on possible thread states seems a bit outdated, as it doesn't even include end and statistics states (which is another one we're seeing often). my.cnf follows: [mysqld] back_log=128 ft_min_word_len=3 max_connections=300 key_buffer_size=320M myisam_sort_buffer_size=256M join_buffer_size=4M read_buffer_size=2M read_rnd_buffer_size=1M sort_buffer_size=4M table_cache=2048 thread_cache_size=150 wait_timeout=1800 connect_timeout=5 max_allowed_packet=16M max_connect_errors=512 tmp_table_size=64M query_prealloc_size=16384 query_cache_limit = 512K query_cache_size = 48M query_cache_type = 1 skip-innodb skip-name-resolve skip-external-locking log-bin ... replication setup directives skipped ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having rows or fields that can't be modified once entered.
I was wondering if there was some builtin mechanism I could use to ensure that a field or row in my database could not be altered after it was initially inserted. Is there anything in the database that would allow this type of functionality? Thanks!! You can give a user certain rights to do things with certain databases or tables. You cannot set the rights for a limited number of rows however. Take a look at http://dev.mysql.com/doc/mysql/en/GRANT.html If you do not GRANT (or if you REVOKE) the rights for this particular user to DELETE or UPDATE the table he/she can only insert new data and select things (and all other things he/she has the rights for). It's good practise to only grant the rights to a user he/she needs to do. For use with (web) applications it's also better to create a new user for that purpose and grant the necessary right. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WinMySqladmin Question
Can anyone here tell me, what does it mean when a process when a Process goes into Sleep State. I see this under the Process tap in the WiN Mysql administrator.. Seems each time I open my database in a program, after it executes and I close it, I have a Process in Sleep mode. any help would be appreciated here. Thank you
Grouping based on state changes
I have the following table definition for time series data: ID (int) time (DATETIME)state (int)value (int) I want to make a state based grouping and calculate the mean of each grouping. The state based grouping should be done by creating a new group whenever the state changes, from one point in time to another. To explain what I mean I have made a small example: ID time state value 1 2004-01-01 00:00 0 5 2 2004-01-01 00:02 0 3 3 2004-01-01 00:04 1 7 4 2004-01-01 00:07 1 9 5 2004-01-01 00:08 1 2 6 2004-01-01 00:10 0 2 7 2004-01-01 00:12 0 1 8 2004-01-01 00:13 2 2 9 2004-01-01 00:14 2 4 10 2004-01-01 00:15 2 2 The grouping for the above table would then be: (1,2); (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8. How do express a select statement in SQL which gives me the mean of each group? Greetings, Mads Lindstrøm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Error Log
Is there any way to find log of queries that returned errors. Syntax errors or column not found, for example? The General Log simply logs the query without giving any information as to whether the query was successful or not Thank you in advance. Best regards, TS
Re: Grouping based on state changes
Use GROUP BY: SELECT state, AVG(value) FROM yourtable GROUP BY state; See the manual for all the details http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html. Michael [EMAIL PROTECTED] wrote: I have the following table definition for time series data: ID (int) time (DATETIME)state (int)value (int) I want to make a state based grouping and calculate the mean of each grouping. The state based grouping should be done by creating a new group whenever the state changes, from one point in time to another. To explain what I mean I have made a small example: ID time state value 1 2004-01-01 00:00 0 5 2 2004-01-01 00:02 0 3 3 2004-01-01 00:04 1 7 4 2004-01-01 00:07 1 9 5 2004-01-01 00:08 1 2 6 2004-01-01 00:10 0 2 7 2004-01-01 00:12 0 1 8 2004-01-01 00:13 2 2 9 2004-01-01 00:14 2 4 10 2004-01-01 00:15 2 2 The grouping for the above table would then be: (1,2); (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8. How do express a select statement in SQL which gives me the mean of each group? Greetings, Mads Lindstrøm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Thank you so much joe..
You have actually have been a great help...
Again thank you so much for your help,
Tom T
At 04:44 PM 9/21/2004, you wrote:
Thomas,
Because the db is lower than version 4 and doesn't support secure
connections, unless the db is on the same box as the web server, then
connection strings are already being passed between your web servers and
the db accross the local network using clear text and those connection
strings are probably more fruitfull targets for internal student hackers
than yours would be. So it sounds to me like like your Net Admin is
putting an impossible requirement on your application that is not required
for apps using more critical data. I don't see a way that you can secure
your connection string as it is sent accross the local network unless they
upgrade the db to a version that supports secure connections.
Sorry I can't be of more help.
Best Regards,
Joe
Thomas Trutt [EMAIL PROTECTED] wrote:
Hi Joe,
My understanding is, and please don't quite me on this one, is
that it is a shared server.. If i'm right the server that my db will be
running on also houses some large db's for our web services, here at
Cornell. The data I'm holding is actually very, very low security, its tick
marks, but it is also the only database being accessed by an outside
client.. The program i wrote is a desktop application that the user can
record tick marks for when they are asked different types of questions at
the refrence and computer operators desk here in the Library. It also
stores Invoice transactions but there is no CC or payment information
stored or used in the actual program. I think what my Net Admins concern is
that someone may get access to my user name and password and somehow gain
access to the server as a whole, even though the user name and password
only has INSERT, DELETE, UPDATE,, and READ, access to my DB.. But i do know
that our servers are usually a nice target for hackers, ie bored computer
Science majors.. :o)
Again many thanks,
Tom T
At 04:00 PM 9/21/2004, you wrote:
Thomas,
http://dev.mysql.com/doc/mysql/search.php?q=ssllang=encharset=iso-8859-1
It looks like SSL/SSH are only available as of MySQL 4 or higher.
Is your Net Admin really worried about eaves dropping of packets on the
local network. It must be very sensitive data. If you already have a MySQL
database on your network that is lower version than 4 then how do the
other apps connect to it securely? Or is your app the only one that needs
this level of security and other app can transmit to the db in clear text?
Joe Audette
Thomas Trutt wrote:
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net
admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the
question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Re: Choosing the correct my.cnf
/etc/my.cnf is the *global* configuration file. Settings for a specific mysql server do not belong there. Server-specific settings go in that server's data directory. This is described in the manual http://dev.mysql.com/doc/mysql/en/Option_files.html and http://dev.mysql.com/doc/mysql/en/Multiple_servers.html. Michael Jonas Ladenfors wrote: Hello, I have two installations of mysql on my computer a stable 4.x version and the aplha 5.x version. I want to use a separate my.cnf file for my 5.x version. But as soon as I start the mysqld_safe it tries to read /etc/my.cnf which belongs to my 4.x server. This of course is not what I want. I have placed the my.cnf in the root directory of my mysql5 directory (/usr/local/mysql5/my.cnf) but I dont know how to force mysqld_safe to read that file and not the one in /etc/my.cnf. Could someone give my a solution on how to fix this? Thanks in advance /Jonas --- Jonas Ladenfors Software engineer Neuronova AB Fiskartorps vägen 15 Stockholm +46 8 786 09 26 +46 73 624 33 89 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where and or ...ughhh!
I'm back, trying to figure my way through a somewhat
confusing set of conditons.
Sort of doing a sanity check here.
First explanation:
I've put together a search (the database) form that
has 6 inputs.
Just to clarify, the fields are
1-JobTitle
2-City
3-Start
4-Industry
5-State
6-TaxTerm.
The idea is that a user can choose one , two or all
fields to put criteria into. Regardless, whichever
one they choose , there should be a records return.
More criteria of course adds more refinement.
Right now I only have 4 of the 6 fields rigged for
action- JobTitle, Start, Industry and State.
I half see what's going on, but unclear on how to set
it all up.
So right now, since Title and Start are divided by an
OR, if I chose both, then there is not refinement, I
get records that meet both criteria , not both
criteria combined , savvy ?
Industry is sort of seperate right now, and State can
be combined with industry to refine the results to
match up all industries within whatever particular
states.
I guess ultimately I need a very long series of OR's
and AND's but not sure if there is something neater
and then operator precedence in a series of AND's and
OR's would probably throw me. At least I think so.
ALright, so Im sure this is one of my inance babbles
again, but if anyone can decipher my cry for
assistance, feedback, morsels of wisdom and knowledge,
I'm listening.
Stuart
code:
where JobTitle like '%{s_JobTitle}%'
or
PostStart = DATE_SUB(CurDate(), Interval
({s_PostStart}) day )
$VendorJobs-ds-SQL.= OR (`VendorJobs`.Industry IN
(.$Projects.));
$VendorJobs-ds-SQL.= AND
(`VendorJobs`.LocationState IN (.$Projs.));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grouping based on state changes
I think all you need is a GROUP BY. Here is a tutorial sample from the manual: http://dev.mysql.com/doc/mysql/en/Counting_rows.html Here is page that describes the full SELECT syntax, including GROUP BY http://dev.mysql.com/doc/mysql/en/SELECT.html And here are all of the other functions you can use with GROUP BY http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Just guessing but your query should look something like: select state , avg(value) as mean , count(value) as population , std(value) as std_deviation , variance(value) as variance from timeseries_data_table group by state Uh- OH I just re-read your example and realized that I had the situation wrong. The second set of state=0 records would be grouped in with the first pair. Without some other means of differentiating one group of state values from another group __by the data__ and not __by their position__, SQL cannot accomplish what you want. The second pair of state=0 records is different from the first pair only because the records before them had a different state value (state=1). SQL is not meant to process information in this type of linear fashion. A cursor-based query _may_ be available to you IF you are running the bleeding edge MySQL server (5.x+). I don't run that version so I can't tell you what's working yet and what isn't. IMHO, I believe you will need to script a solution that scrolls through the records in sequence in order to detect the change in state and compute each group's statistics on the fly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/22/2004 07:48:45 AM: I have the following table definition for time series data: ID (int) time (DATETIME)state (int)value (int) I want to make a state based grouping and calculate the mean of each grouping. The state based grouping should be done by creating a new group whenever the state changes, from one point in time to another. To explain what I mean I have made a small example: ID time state value 1 2004-01-01 00:00 0 5 2 2004-01-01 00:02 0 3 3 2004-01-01 00:04 1 7 4 2004-01-01 00:07 1 9 5 2004-01-01 00:08 1 2 6 2004-01-01 00:10 0 2 7 2004-01-01 00:12 0 1 8 2004-01-01 00:13 2 2 9 2004-01-01 00:14 2 4 10 2004-01-01 00:15 2 2 The grouping for the above table would then be: (1,2); (3,4,5); (6,7); (8,9,10), as the state changed at ID 3, 6, and 8. How do express a select statement in SQL which gives me the mean of each group? Greetings, Mads Lindstrøm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing the option file
Hi, Actually i want mysql 4.0 classic version which donot support innodb engine. As classic version is a commercial base one, i installed the mysql 4.0 standard one (which includes innodb). Here my problem is , i have to inactivate the innodb engine. For that what we need to do is From MySQL 4.0 on, the InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file. but my problem is how to find out the MySQL option file, and in that how to add that skip-innodb option. Please advise me in this asap. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Custom Auto-Increment Problem
Actually it's not a total and can't be calculated. The idea is that as users (with individual id_client keys) add rows the id of the row is auto incremented for their key only. Example: If user A adds 3 rows: id id_client --- 1 A 2 A 3 A and then user B adds 2 rows id id_client --- 1 A 2 A 3 A 1 B 2 B I am not looking for a sum of each client's records... that's an easy query... I need the auto incremental id's for each client. Dan T -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: Wednesday, September 22, 2004 3:24 AM To: [EMAIL PROTECTED] Subject: Re: Custom Auto-Increment Problem In article [EMAIL PROTECTED], Dan Tappin [EMAIL PROTECTED] writes: Hi Shawn, First off thanks for the tip. I had read that page once already but after reading twice again after your post I realized that the answer was right there. Wrapping that concept around my brain really hurt but I get it now. I had this: CREATE TABLE projects ( id int auto_increment, id_project int, id _client, PRIMARY KEY (id) ) When I should have this: CREATE TABLE projects ( id int auto_increment, id_client int, PRIMARY KEY (id_client, id) ) I would not do that since it's absolutely nonstandard and works only with MyISAM tables. Apparently id_clientkey is the number of records with the same id_client and smaller ids. This can be easily calculated on the fly and thus should not be stored in the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Hi Stuart,
Getting your brains warped by logical statements, eh?
If I remember correctly AND has precedence over OR. That means that the
statement A or B and C evaluates to A or (B and C) which means that
the statement will be true if A is true or if both B and C are true.
Because of this precedence issue, you need to use parentheses to specify a
new order of evaluation. This phrase, (A or B) AND C will be true only
if either A or B is true at the same time that C is also true. That is a
much different meaning than if the same statement were written without
parentheses.
Basically OR adds records to your results (less restrictive), AND takes
them away (more restrictive).
The easiest place to deal with the widely different request of your users
is in your input processing script. Use your scripting language to build
an appropriate query based on the options they provide. No single SQL
statement will handle BOTH every combination of user input AND process
quickly. You should build custom WHERE clauses based on your user's
input. This is definitely NOT a one-size-fits-all situation.
Remember to use parentheses. When in doubt, spell it out. Don't make the
query engine read your mind.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein [EMAIL PROTECTED] wrote on 09/22/2004 09:46:46 AM:
I'm back, trying to figure my way through a somewhat
confusing set of conditons.
Sort of doing a sanity check here.
First explanation:
I've put together a search (the database) form that
has 6 inputs.
Just to clarify, the fields are
1-JobTitle
2-City
3-Start
4-Industry
5-State
6-TaxTerm.
The idea is that a user can choose one , two or all
fields to put criteria into. Regardless, whichever
one they choose , there should be a records return.
More criteria of course adds more refinement.
Right now I only have 4 of the 6 fields rigged for
action- JobTitle, Start, Industry and State.
I half see what's going on, but unclear on how to set
it all up.
So right now, since Title and Start are divided by an
OR, if I chose both, then there is not refinement, I
get records that meet both criteria , not both
criteria combined , savvy ?
Industry is sort of seperate right now, and State can
be combined with industry to refine the results to
match up all industries within whatever particular
states.
I guess ultimately I need a very long series of OR's
and AND's but not sure if there is something neater
and then operator precedence in a series of AND's and
OR's would probably throw me. At least I think so.
ALright, so Im sure this is one of my inance babbles
again, but if anyone can decipher my cry for
assistance, feedback, morsels of wisdom and knowledge,
I'm listening.
Stuart
code:
where JobTitle like '%{s_JobTitle}%'
or
PostStart = DATE_SUB(CurDate(), Interval
({s_PostStart}) day )
$VendorJobs-ds-SQL.= OR (`VendorJobs`.Industry IN
(.$Projects.));
$VendorJobs-ds-SQL.= AND
(`VendorJobs`.LocationState IN (.$Projs.));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
My brain is warped by web development in general ;)
Thanks for your response. After I sent this , I got
an email from the PHP list, with a good chunk of code
to show how it's done. Yes, scripting is the only way
for this type of query.
Thank you for the response.
Stuart
--- [EMAIL PROTECTED] wrote:
Hi Stuart,
Getting your brains warped by logical statements,
eh?
If I remember correctly AND has precedence over OR.
That means that the
statement A or B and C evaluates to A or (B and
C) which means that
the statement will be true if A is true or if both B
and C are true.
Because of this precedence issue, you need to use
parentheses to specify a
new order of evaluation. This phrase, (A or B) AND
C will be true only
if either A or B is true at the same time that C is
also true. That is a
much different meaning than if the same statement
were written without
parentheses.
Basically OR adds records to your results (less
restrictive), AND takes
them away (more restrictive).
The easiest place to deal with the widely different
request of your users
is in your input processing script. Use your
scripting language to build
an appropriate query based on the options they
provide. No single SQL
statement will handle BOTH every combination of user
input AND process
quickly. You should build custom WHERE clauses
based on your user's
input. This is definitely NOT a one-size-fits-all
situation.
Remember to use parentheses. When in doubt, spell it
out. Don't make the
query engine read your mind.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein [EMAIL PROTECTED] wrote on
09/22/2004 09:46:46 AM:
I'm back, trying to figure my way through a
somewhat
confusing set of conditons.
Sort of doing a sanity check here.
First explanation:
I've put together a search (the database) form
that
has 6 inputs.
Just to clarify, the fields are
1-JobTitle
2-City
3-Start
4-Industry
5-State
6-TaxTerm.
The idea is that a user can choose one , two or
all
fields to put criteria into. Regardless,
whichever
one they choose , there should be a records
return.
More criteria of course adds more refinement.
Right now I only have 4 of the 6 fields rigged for
action- JobTitle, Start, Industry and State.
I half see what's going on, but unclear on how to
set
it all up.
So right now, since Title and Start are divided by
an
OR, if I chose both, then there is not refinement,
I
get records that meet both criteria , not both
criteria combined , savvy ?
Industry is sort of seperate right now, and State
can
be combined with industry to refine the results to
match up all industries within whatever particular
states.
I guess ultimately I need a very long series of
OR's
and AND's but not sure if there is something
neater
and then operator precedence in a series of AND's
and
OR's would probably throw me. At least I think
so.
ALright, so Im sure this is one of my inance
babbles
again, but if anyone can decipher my cry for
assistance, feedback, morsels of wisdom and
knowledge,
I'm listening.
Stuart
code:
where JobTitle like '%{s_JobTitle}%'
or
PostStart = DATE_SUB(CurDate(), Interval
({s_PostStart}) day )
$VendorJobs-ds-SQL.= OR (`VendorJobs`.Industry
IN
(.$Projects.));
$VendorJobs-ds-SQL.= AND
(`VendorJobs`.LocationState IN (.$Projs.));
--
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: Where and or ...ughhh!
Hi, Hi Stuart, Getting your brains warped by logical statements, eh? If I remember correctly AND has precedence over OR. That means that the statement A or B and C evaluates to A or (B and C) which means that the statement will be true if A is true or if both B and C are true. When in doubt, just think about the notation used in boolean algebra: A or B and C = A+B*C This looks more familiar, and the precedence used is the same. - Cs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom Auto-Increment Problem
In article [EMAIL PROTECTED], Dan Tappin [EMAIL PROTECTED] writes: Actually it's not a total and can't be calculated. The idea is that as users (with individual id_client keys) add rows the id of the row is auto incremented for their key only. Example: If user A adds 3 rows: idid_client --- 1 A 2 A 3 A and then user B adds 2 rows idid_client --- 1 A 2 A 3 A 1 B 2 B I am not looking for a sum of each client's records... that's an easy query... I need the auto incremental id's for each client. Let's add an ordinary auto_increment column named ser (for serial): ser id_client id 1 A 1 2 A 2 3 A 3 4 B 1 5 B 2 Now the query SELECT t1.ser, t1.id, count(t2.id) + 1 AS calc_id FROM tbl t1 LEFT JOIN tbl t2 ON t2.ser t1.ser AND t2.id_client = t1.id_client GROUP BY t1.ser, t1.id shows you that calc_id = id for all rows, which means that id can be calculated. The only difference is when you delete rows inbetween. In this case id_calc will be less than id. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Custom Auto-Increment Problem
-Original Message- From: Harald Fuchs Sent: Wednesday, September 22, 2004 9:39 AM To: [EMAIL PROTECTED] Subject: Re: Custom Auto-Increment Problem The only difference is when you delete rows inbetween. In this case id_calc will be less than id. I stand corrected. Yes it can be calculated but however the deletion of rows makes the calculation unreliable. The id number has to be constant regardless of row deletion. I can't have the id changing thus that's why I am storing it. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Like question
Hello there,
I was wondering how I could make a specific type of search when the string has more
than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word with the
fields. The problem is that I can't control how many fields should be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clean Reinstall
I am extremely frustrated with the root passwordproblem. I read the manual and followed the instructions but it just won't work. I tried setting a newpassword but then when I insert the new passoword it says the password it wrong. In any case...I want to uninstall MySQL and try a new clean install. What would be the procedure to do a VERY clean install? Thanks in advance, FayeC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like question
RuiSMonteiro [EMAIL PROTECTED] wrote on 22/09/2004 09:35:00:
Hello there,
I was wondering how I could make a specific type of search when the
string has more than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each
word with the fields. The problem is that I can't control how many
fields should be compared.
Also don't know how to compare each word. The following syntax doesn't
work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
I suspect that what you want is a Fulltext index. This splits the text up
into words and does a separate search for the separate words. This is much
more efficient than the LIKE search for the case when you need leading %
characters, because this forces a full linear search. The fulltext search
would look something like
MATCH fruits.color AGAINST (Green, Apple)
Unfortunately, Fulltext searches are available only on MyISAM tables.
See http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date BETWEEN Question
Michael, Thanks for the response. My query was kind of long, so I was just trying to simplify. Should have included it anyways. Here is my query: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed = '2004-07-21 00:00:00' AND TRACKING.completed = '2004-07-23 23:59:59' order by TRACKING.jobid desc The BETWEEN statement is similar: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed BETWEEN '2004-07-21 00:00:00' AND '2004-07-23 23:59:59' order by TRACKING.jobid desc Oddly enough, when I ran what you suggested: select * from TRACKING where completed = '2004-07-21' Empty set! But there are rows in the table with that date. If I run either: select * from TRACKING where completed '2004-07-21' - or - select * from TRACKING where completed = '2004-07-21' I get the desired results, particularly in the last case where I get dates with 2004-07-21! I do have a standard INDEX on the completed field, would that have something to do with it? Again, completed is a DATETIME field. I have this same table duplicated on 3.23.58 and 4.0.17 with the same results. I'm probably just missing something small that I can't see, but frustrating nonetheless. Thanks, Jeremy Jeremy Brown [InfoSend] wrote: Hello, I currently have a table with a completed DATETIME field. I am trying to run a query that will return all rows *inclusive* of the start and end dates. I have tried the following query: SELECT `name`, `completed` FROM `table` WHERE `completed` BETWEEN '2004-07-21' AND '2004-07-23'; Why are you comparing completed to DATEs, if completed is a DATETIME? Note that '2004-07-23' is '2004-07-23 00:00:00' when interpreted as a DATETIME. The problem is that this query will only return rows from 2004-07-22, and does not include rows from 2004-07-21 or 2004-07-23, like I need it to. This could be the desired behavior for the BETWEEN operator (I couldn't seem to find any documentation of it in the documents for some reason), so I also tried: BETWEEN is inclusive of the endpoints. This is documented in the manual http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html. SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21' AND `completed` = '2004-07-23'; This does the same thing, it only returns dates from 2004-07-22 without including the start or end date. This tells us BETWEEN is not the problem, as the inequalities produce the same result. Something else is going on. Something strange, but something else. I have tried adding a time (e.g. 2004-07-21 00:00:00 and 2004-07-23 23:59:59), but again, only 2004-07-22 is returned. If I remove one of the conditions, I get the correct result. e.g.: SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21'; What does SELECT name, completed FROM `table` WHERE completed = '2004-07-21'; return? Will return all rows on *and* after 2004-07-21, as desired. But with both operators, it does not work. I have tried this query on both 3.23.58 and 4.0.17. I searched the net over and could not find a definitive answer to this problem, so apologies if it's been answered before. My guess is that your real query is more complicated than what you've shown us (your table isn't really named table, is it?), and there is some interaction which accounts for this. Perhaps if you showed the full query and the table definition someone could point out the problem/solution. TIA, Jeremy Brown Michael -- 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]
innoDB alter table and locking issues
Hello List, Recently I needed to alter a large innodb table (~200k rows) to add a column on a live server. The alter table ran fine on the development server with close to the same number of rows in the table so I didn't anticipate too much trouble. The list archive and the documentation indicate that alter table has some inneficenties - like rebuilding the entire table, indexes and all and that the table would be locked as read only while the alter table command was executing -- and the the alter table was likely to take A LONG TIME. (one poor guy posted that he gave up on altering his table after it was still running for 24 hours!) What we found is that the process list quickly filled up with SELECT statements that were 'Waiting for table' -- The connections maxed out (at 500) since the webservers were still sending queries, which I figured would be okay since they are reads, but apparantly these queries were locked out. I found myself locked out , unable to issue more commands and had to kill mysqld. Since all ALTER TABLE commands will probably exhibit suimilar locking - I am weary of even creating a new table with another name with the new structure, populating it with data from the table I want to alter, dropping the old table and renaming the new one back to the old one's name. So I'm wondering a few things if people can chime in: - does ALTER TABLE really just lock read only or does it just flat out LOCK? - might there be some configuration setting that influences this locking? - what strategies do people use to alter live busy tables ? Thanks for any and all input! Jeff Kolber ps: we are using: mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb - datafiles on an ipstore disk appliance
We are looking at a disk subsystem for a high transactional application. And management wishes to use disk appliance (IPStore or NetAPP). Does anyone know if placing the mysql MYISAM and INNODB datafiles on IPStore or NetApp disk applliances are supported? (We use almost all INNODB tables) If the disk appliance goes away, will it corrupt the database? Any thoughts on this would be appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
On Wed, Sep 22, 2004 at 01:52:36PM -0400, Debbie L wrote: We are looking at a disk subsystem for a high transactional application. And management wishes to use disk appliance (IPStore or NetAPP). Does anyone know if placing the mysql MYISAM and INNODB datafiles on IPStore or NetApp disk applliances are supported? I'm not sure what you mean by supported (commercial paid support?), but it works. (We use almost all INNODB tables) If the disk appliance goes away, will it corrupt the database? Maybe not corrupt but it could lead to lost transactions. Where do you plan on storing the transaction logs? Also on the appliance or locally? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Depending on how exact the returned values need to be, and how much data you have to sift through, you could do. ( perl example ) $job=$dbh-quote($job.'%'); $city=$dbh-quote($city.'%'); $start=$dbh-quote($start.'%); . . . $select = SELECT * from myfile where jobtitle like $job and city like $city and start like $start and industry like $industry and state like $state and taxterm like $taxterm; . . do the actual SQL call here. Stuart Felenstein wrote: I'm back, trying to figure my way through a somewhat confusing set of conditons. Sort of doing a sanity check here. First explanation: I've put together a search (the database) form that has 6 inputs. Just to clarify, the fields are 1-JobTitle 2-City 3-Start 4-Industry 5-State 6-TaxTerm. The idea is that a user can choose one , two or all fields to put criteria into. Regardless, whichever one they choose , there should be a records return. More criteria of course adds more refinement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
Your right, I shouldn't say supported... Is it a wise to put datafiles on a disk appliance? Coming from other database background, it is not wise to do such a thing and will cause problems when the disk appliance has problems. As for the transaction logs, I haven't reallly thought of it, but to my understanding management wants it all on the appliance. The only other choice I have for the logs would be an internal drive. On Wed, 22 Sep 2004 11:07:30 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote: On Wed, Sep 22, 2004 at 01:52:36PM -0400, Debbie L wrote: We are looking at a disk subsystem for a high transactional application. And management wishes to use disk appliance (IPStore or NetAPP). Does anyone know if placing the mysql MYISAM and INNODB datafiles on IPStore or NetApp disk applliances are supported? I'm not sure what you mean by supported (commercial paid support?), but it works. (We use almost all INNODB tables) If the disk appliance goes away, will it corrupt the database? Maybe not corrupt but it could lead to lost transactions. Where do you plan on storing the transaction logs? Also on the appliance or locally? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date BETWEEN Question
You still did not send your table definitions. Jeremy Brown [InfoSend] wrote: Michael, Thanks for the response. My query was kind of long, so I was just trying to simplify. Should have included it anyways. Here is my query: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed = '2004-07-21 00:00:00' AND TRACKING.completed = '2004-07-23 23:59:59' order by TRACKING.jobid desc The BETWEEN statement is similar: select REFCLIENTS.sample, TRACKING.* from REFCLIENTS, TRACKING where TRACKING.ccode LIKE 'ABC' AND REFCLIENTS.code = TRACKING.ccode AND TRACKING.jobtype = 1 AND TRACKING.completed BETWEEN '2004-07-21 00:00:00' AND '2004-07-23 23:59:59' order by TRACKING.jobid desc Oddly enough, when I ran what you suggested: select * from TRACKING where completed = '2004-07-21' Empty set! But there are rows in the table with that date. That is expected if completed is a datetime field. You only supplied the date portion. try: select * from TRACKING where completed like '2004-07-21%' If I run either: select * from TRACKING where completed '2004-07-21' - or - select * from TRACKING where completed = '2004-07-21' I get the desired results, particularly in the last case where I get dates with 2004-07-21! I do have a standard INDEX on the completed field, would that have something to do with it? Again, completed is a DATETIME field. I have this same table duplicated on 3.23.58 and 4.0.17 with the same results. I'm probably just missing something small that I can't see, but frustrating nonetheless. Thanks, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
On Wed, Sep 22, 2004 at 02:22:59PM -0400, Debbie L wrote: Your right, I shouldn't say supported... Is it a wise to put datafiles on a disk appliance? Coming from other database background, it is not wise to do such a thing and will cause problems when the disk appliance has problems. Well, it's very common in the world of big iron databases. I know that man companies routinely store A LOT of their Oracle data on Netapp or EMC gear. Some are doing it with MySQL now too. As for reliability, these disk systems tend to be far more reliable than the hosts connecting to them (at least on the low end). There's a lot of redundancy because they're designed for just this sort of application. As for the transaction logs, I haven't reallly thought of it, but to my understanding management wants it all on the appliance. That's how I'd do it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1.5 FreeBSD 4.10 compile error
Making all in sql make all-recursive Making all in share ../../extra/comp_err -C./charsets/ danish/errmsg.sys Usage: ../../extra/comp_err [-?] [-I] [-V] fromfile[s] tofile *** Error code 255 what's this error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
The returned values should be exact. As far as how much data, I'm assuming your talking about amount of records ? Potentially eventually maybe quite a bit. Currently mysql statement is comprised of select and from with all the proper joins. But Im confused, when you say below actual SQL call here, is the referring to the SQL statement that would look at all the imput values and process the data ? like a series of and's or or's ? Stuart --- gerald_clark [EMAIL PROTECTED] wrote: Depending on how exact the returned values need to be, and how much data you have to sift through, you could do. ( perl example ) $job=$dbh-quote($job.'%'); $city=$dbh-quote($city.'%'); $start=$dbh-quote($start.'%); . . . $select = SELECT * from myfile where jobtitle like $job and city like $city and start like $start and industry like $industry and state like $state and taxterm like $taxterm; . . do the actual SQL call here. Stuart Felenstein wrote: I'm back, trying to figure my way through a somewhat confusing set of conditons. Sort of doing a sanity check here. First explanation: I've put together a search (the database) form that has 6 inputs. Just to clarify, the fields are 1-JobTitle 2-City 3-Start 4-Industry 5-State 6-TaxTerm. The idea is that a user can choose one , two or all fields to put criteria into. Regardless, whichever one they choose , there should be a records return. More criteria of course adds more refinement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Entries
Hi, Yes ofcourse it is a primary key with two fields. The insert exists whenever it has a duplicate entry, it doesn't continue with further records. Is there any scope to resolve it. Thanks Suresh Eldo Skaria wrote: Hi, Suresh may be expressing that he has a primary key with two fields. Eldo. On Tue, 21 Sep 2004 19:32:17 -0400, Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Suresh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 6:43 PM Subject: Duplicate Entries Hello All, I am porting from 4.0.0-alpha-nt to 4.0.1-alpha-nt. In which i have a table with two primary key, my older mysql server insert all the records except the duplicate fields(Primary Key). Whereas in the new mysql server it exits whenever it sees a duplicate entry. How to resolve it ? Am I understanding you correctly? It *sounds* like you're saying that you have a table with TWO primary keys. As far as I know, it is not possible to have two separate primary keys in a single table under any circumstances. Do you mean that you have a single primary key that has two columns in it? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Stuart. If your queries must match _all_ of your input variables, you are in the situation where you only need ANDs (no ORs needed). Just create one term in your WHERE statement for each field they filled in and make sure that there is an AND between each of them in the right places. This should be a piece of simple string building along the same lines you are already doing. I think that the reason that nobody, including myself, can tell you how to write your script is because you never told us _your_ rules on how each field is supposed to be handled. Build your WHERE clause so that it meets _your_ requirements and it should work correctly. Of course, no input from the user means you don't need a WHERE clause at all. You can regurgitate your entire database. This may be something you want to avoid by requiring at least 1 or 2 input values. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/22/2004 03:11:16 PM: The returned values should be exact. As far as how much data, I'm assuming your talking about amount of records ? Potentially eventually maybe quite a bit. Currently mysql statement is comprised of select and from with all the proper joins. But Im confused, when you say below actual SQL call here, is the referring to the SQL statement that would look at all the imput values and process the data ? like a series of and's or or's ? Stuart --- gerald_clark [EMAIL PROTECTED] wrote: Depending on how exact the returned values need to be, and how much data you have to sift through, you could do. ( perl example ) $job=$dbh-quote($job.'%'); $city=$dbh-quote($city.'%'); $start=$dbh-quote($start.'%); . . . $select = SELECT * from myfile where jobtitle like $job and city like $city and start like $start and industry like $industry and state like $state and taxterm like $taxterm; . . do the actual SQL call here. Stuart Felenstein wrote: I'm back, trying to figure my way through a somewhat confusing set of conditons. Sort of doing a sanity check here. First explanation: I've put together a search (the database) form that has 6 inputs. Just to clarify, the fields are 1-JobTitle 2-City 3-Start 4-Industry 5-State 6-TaxTerm. The idea is that a user can choose one , two or all fields to put criteria into. Regardless, whichever one they choose , there should be a records return. More criteria of course adds more refinement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Right, I thought the rules were mentioned in the first post. Apoplogies. The user should be able to enter one value or all 6 and with each additional value added from none, the results should be more refined. i.e. Alabama chosen only - all jobs in Alabama come back Alabama and Financing - all financing jobs in Alabama come back Alabama , Financing and 5 days old, all financing jobs in Alabama within the last 5 days come back Alabam, Financing, 5 days old, and treasurer, all financing jobs in Alabama, no more then 5 days old with the job title of treasurer. You raise a good point , that I thought about and then cast to the side temporarily. No I don't want all data coming back, so probably with no values entered at all then no records come back. From my understanding now , to do something like this the query needs to be created on the fly withink the script. So if no values exist, no array exists, on to the next field with an OR, if values exists, take the values input into an array , onto next field with an AND. I think that is it , now if I only knew how to code ;) Stuart --- [EMAIL PROTECTED] wrote: Stuart. If your queries must match _all_ of your input variables, you are in the situation where you only need ANDs (no ORs needed). Just create one term in your WHERE statement for each field they filled in and make sure that there is an AND between each of them in the right places. This should be a piece of simple string building along the same lines you are already doing. I think that the reason that nobody, including myself, can tell you how to write your script is because you never told us _your_ rules on how each field is supposed to be handled. Build your WHERE clause so that it meets _your_ requirements and it should work correctly. Of course, no input from the user means you don't need a WHERE clause at all. You can regurgitate your entire database. This may be something you want to avoid by requiring at least 1 or 2 input values. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 09/22/2004 03:11:16 PM: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where and or ...ughhh!
Stuart Felenstein wrote: The returned values should be exact. As far as how much data, I'm assuming your talking about amount of records ? Potentially eventually maybe quite a bit. Currently mysql statement is comprised of select and from with all the proper joins. But Im confused, when you say below actual SQL call here, is the referring to the SQL statement that would look at all the imput values and process the data ? like a series of and's or or's ? I built the query. You need to execute it and read the returned set. I don't know what language you are using, so I did not go into detail. The trick is to use 'LIKE' and append the '%' wildcard to the values entered. Now you can use AND between each condition. You must be sure to properly quote each string using the languages quoting function. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL data files
Shutdown mysql, move data files, create symlink to datafile(s) on other partition/disk/whatever.. - Original Message - From: VijayKumar Dogra [EMAIL PROTECTED] Date: Wed, 22 Sep 2004 11:22:55 +0530 Subject: MySQL data files To: [EMAIL PROTECTED] Hello All, Is there any way by which I can store data files of mysql in other partition of my system Regards, VijayKumar Dogra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Clean Reinstall
I do not know if this is the right way or not but this is how I have done it: Delete Mysql-Version folder, from where you installed MySQL. It is normally in '/usr/local' folder. Then re-install MySQL. Hope it works. Kirti -Original Message- From: FayeC SQL [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 12:39 PM To: MySQL List Subject: Clean Reinstall I am extremely frustrated with the root passwordproblem. I read the manual and followed the instructions but it just won't work. I tried setting a newpassword but then when I insert the new passoword it says the password it wrong. In any case...I want to uninstall MySQL and try a new clean install. What would be the procedure to do a VERY clean install? Thanks in advance, FayeC -- 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]
SELECT a percentage range of a given value
Hello, I have a music database containing song BPM (Beats Per Minute) data values stored as FLOAT. I need to pull a range of BPM values based on a user-supplied integer. The range should pull all records which are 8% higher and lower than the given integer. I tried this query for starters: SELECT * FROM test HAVING ( bpm SUM(100 * 1.08) ) OR ( bpm SUM(100 * .92) ) No errors appeared however only one row returned (with bpm value 55.03). There are approximately 100 records that have a bpm value between 92.00 and 108.00 in the test database. Am I overlooking something obvious?
Re: SELECT a percentage range of a given value
At 03:11 PM 9/22/2004, you wrote: Hello, I have a music database containing song BPM (Beats Per Minute) data values stored as FLOAT. I need to pull a range of BPM values based on a user-supplied integer. The range should pull all records which are 8% higher and lower than the given integer. I tried this query for starters: SELECT * FROM test HAVING ( bpm SUM(100 * 1.08) ) OR ( bpm SUM(100 * .92) ) No errors appeared however only one row returned (with bpm value 55.03). There are approximately 100 records that have a bpm value between 92.00 and 108.00 in the test database. Am I overlooking something obvious? Yup, your calculations should have used an AND instead of an OR and you don't need SUM(). For something simple try this: select * form test where BMP between 100*.92 and 100*1.08 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Entries
Suresh wrote: Hi, Yes ofcourse it is a primary key with two fields. The insert exists whenever it has a duplicate entry, it doesn't continue with further records. Is there any scope to resolve it. Thanks Suresh Eldo Skaria wrote: insert ignore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT a percentage range of a given value
SELECT * FROM test WHERE bpm ( 100 * 1.08 ) AND bpm (100*.92) Stembridge, Michael wrote: Hello, I have a music database containing song BPM (Beats Per Minute) data values stored as FLOAT. I need to pull a range of BPM values based on a user-supplied integer. The range should pull all records which are 8% higher and lower than the given integer. I tried this query for starters: SELECT * FROM test HAVING ( bpm SUM(100 * 1.08) ) OR ( bpm SUM(100 * .92) ) No errors appeared however only one row returned (with bpm value 55.03). There are approximately 100 records that have a bpm value between 92.00 and 108.00 in the test database. Am I overlooking something obvious? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clean Reinstall
FayeC SQL wrote:
I am extremely frustrated with the root passwordproblem. I read the
manual and followed the instructions but it just won't work.
I tried setting a newpassword but then when I insert the new passoword
it says the password it wrong.
In any case...I want to uninstall MySQL and try a new clean install.
What would be the procedure to do a VERY clean install?
Thanks in advance,
FayeC
Faye,
It might be useful if you send what syntax you used to update/create the
password and what syntax you used when trying to log in. Of couse don't
send the real password.
ie -
mysql UPDATE user SET Password=PASSWORD('bla') where user='root';
mysql FLUSH PRIVILEGES;
mysql quit;
[EMAIL PROTECTED] tmp] mysql -u root -p
.
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help!!!!!!!!!!
Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table bb1_designelements, 78 rows affected). Use EXPLAIN to optimize your query. I get this error on every products i tested
Like - problems
Hello there,
I was wondering how I could make a specific type of search when the string has more
than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word with the
fields. The problem is that I can't control how many fields should be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crosstab/Transpose Query Help
I'm trying to perform a query where I transpose cell values from a table into column names with totals. For example, I have the following table and I'd like to produce the view at the bottom: SitePartQuantity Site APart 15 Site APart 23 Site APart 31 Site BPart 17 Site BPart 310 Site CPart 22 Site CPart 45 I would like to take said view and produce a view as follows SitePart 1Part 2Part 3Part 4 Site A531 Site B7 10 Site C 25 As you can see, all Sites have been grouped and part names have been created as columns. Then part quantities are aggregated. Can anyone offer any guidance? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like - problems
At 04:17 PM 9/22/2004, you wrote:
Hello there,
I was wondering how I could make a specific type of search when the string
has more than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word
with the fields. The problem is that I can't control how many fields
should be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rus,
Use FullText search if you're using a MyISAM table. It will be
much faster. See http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Indexing
Hey all, I have a question about indexing part of a date field. I have a query that I run on a regular basis to retrieve monthly sales numbers: SELECT SUM(OrderSubTotal) FROM tblOrders WHERE DATE_FORMAT(ShipDate, '%Y-%m') = '2004-09'; ShipDate is a date field. My question is how I can phrase the query (or re-index ShipDate) so that MySQL uses the ShipDate index? I've tried figuring out how to index part of a date field, and I can't seem to find any way to do that. I've also tried changing the statement to ShipDate like '2004-09%', as well as the MONTH and YEAR functions, but none of them utilize the index. Is there another, more efficient way to phrase it so the index is used? I'm using MySQL 3.23.53a. The Explain: | table| type | possible_keys | key | key_len | ref | rows | Extra | | tblOrders | ALL | NULL | NULL |NULL | NULL | 122543 | where used | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT a percentage range of a given value
Ah, this is not as complex as I imagined. Thanks! -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 4:52 PM To: Stembridge, Michael Cc: [EMAIL PROTECTED] Subject: Re: SELECT a percentage range of a given value SELECT * FROM test WHERE bpm ( 100 * 1.08 ) AND bpm (100*.92) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ot: GMail invites
Still have GMail invites, for anybody that is interested. E-mail me. (reply off list) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help!!!!!!!!!!
Sven, This warning is telling you there your query is going to execute very slowly. The EXPLAIN that it talks about is mentioned in the manual here: http://dev.mysql.com/doc/mysql/en/EXPLAIN.html You should also search the manual for sections on query optimization and index usage. Here is another document that will help you get help online easier. http://www.catb.org/~esr/faqs/smart-questions.html -- Eric Bergen [EMAIL PROTECTED] On Sun, 19 Sep 2004 07:50:14 +0200, Sven [EMAIL PROTECTED] wrote: Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table bb1_designelements, 78 rows affected). Use EXPLAIN to optimize your query. I get this error on every products i tested -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help!!!!!!!!!!
You're probably going to need to create some indexes on your tables. If you have no indexes, MySQL can only do a full tablescan. See the manual for the syntax of the statement that creates an index. Rhino - Original Message - From: Sven [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, September 19, 2004 1:50 AM Subject: help!! Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table bb1_designelements, 78 rows affected). Use EXPLAIN to optimize your query. I get this error on every products i tested -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL data files
Sure you can. Just move your current data to the location you like to use and then do either of the followings: 1) supply parameter --datadir during startup (or equivalent in my.cnf file) 2) Create a symlink from old location to new location - Original Message - From: VijayKumar Dogra [EMAIL PROTECTED] Date: Wed, 22 Sep 2004 11:22:55 +0530 Subject: MySQL data files To: [EMAIL PROTECTED] Hello All, Is there any way by which I can store data files of mysql in other partition of my system Regards, VijayKumar Dogra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb - datafiles on an ipstore disk appliance
We have been using innodb tables on our systems and we use netapp to store all the tables. It will be however wise to put transaction logs into a different netapp or somewhere else so that even if the netapp goes kaput (highly unlikely) totally you do not lose any data. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choosing the option file
How do you start your mysql now ? In any case you need to supply the --skip-innodb option either in the option file (which most likely you don't have) or at the startup time. If you use safe_mysqld script to start your installation just pass on --skip-innodb to it. And even if you don't, it is not going to harm anything as far as I know. On Wed, 22 Sep 2004 19:45:03 +0530, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Actually i want mysql 4.0 classic version which donot support innodb engine. As classic version is a commercial base one, i installed the mysql 4.0 standard one (which includes innodb). Here my problem is , i have to inactivate the innodb engine. For that what we need to do is From MySQL 4.0 on, the InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file. but my problem is how to find out the MySQL option file, and in that how to add that skip-innodb option. Please advise me in this asap. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
empty user catalog entry for user column
Hello All, For a new build I am seeing that mysql_install_db is putting entries in user table where user= in addition to the correct entries for root. See below mysql select * from user where user=; +-+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | Host| User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | +-+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ | localhost | | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| | | | | 0 | 0 | 0 | | testd4.a.com | | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N | | | | | 0 | 0 | 0 | +-+--+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-+--+---+-+-+ This is creating problem in conencting when I use -h full-domain-name. For localhost it work. Any reason that MySQL is creating these entries in addtion to the correct entries for root. Any explanation??? Regards,
Grants
Hi, I juste installed mysq. 5.1 (alpha) and i guess i am facing some really stupid issue :) I logged in as root without probleme create a database xoops which shows when i do a show databases Now i try to give grants to a user on that datase via grant all on xoops.* to [EMAIL PROTECTED] identified by 'something'; But i keep getting this error ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xoops' Usually this works well, unless i am too sleepy to see my mistake :) What could be the probleme ? Thanks Yann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Indexing
If you want to use the index, you cannot put the column through any function. You have to compare the column value, as is, to constants. For example, SELECT SUM(OrderSubTotal) FROM tblOrders WHERE ShipDate BETWEEN '2004-09-01' AND '2004-09-30'; should do what you want. Michael Andrew Kreps wrote: Hey all, I have a question about indexing part of a date field. I have a query that I run on a regular basis to retrieve monthly sales numbers: SELECT SUM(OrderSubTotal) FROM tblOrders WHERE DATE_FORMAT(ShipDate, '%Y-%m') = '2004-09'; ShipDate is a date field. My question is how I can phrase the query (or re-index ShipDate) so that MySQL uses the ShipDate index? I've tried figuring out how to index part of a date field, and I can't seem to find any way to do that. I've also tried changing the statement to ShipDate like '2004-09%', as well as the MONTH and YEAR functions, but none of them utilize the index. Is there another, more efficient way to phrase it so the index is used? I'm using MySQL 3.23.53a. The Explain: | table| type | possible_keys | key | key_len | ref | rows | Extra | | tblOrders | ALL | NULL | NULL |NULL | NULL | 122543 | where used | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reason for a slow remove index-operation?
Hi, I have a MyISAM-table with a couple of fields, and about half a million rows (datafile is 15MB, indexfile is 10MB) . The server is running under MySQL 4.0.21. Creating an index on a field takes about two minutes. That's fully expected, as the server has to perform an initial sorting for all 50 rows. When I try to remove the index, it takes about one minute. I'm just not sure why this operation is taking so long. I would have thought that the index nearly could be cut right out of the index file, since no meddling with the datafile or the individual rows in the index, or any recalculation of trees would have to take place. When dropping the index there is almost full utilization of the cpu, and SHOW PROCESSLIST for most of the time shows the state as copy to tmp table (and then a few seconds of Repair by sorting, when dropping the index). I wouldn't think there is an i/o-issue, since the file is pretty small and the cpu is utilized (instead of a high iowait-state). What is the reason for an index drop to take that amount of time? (I'm not dropping indexes on a daily basis - my question is mainly out of curiousity :) ) -- - Peter Brodersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for a slow remove index-operation?
In the last episode (Sep 23), Peter Brodersen said: I have a MyISAM-table with a couple of fields, and about half a million rows (datafile is 15MB, indexfile is 10MB) . The server is running under MySQL 4.0.21. Creating an index on a field takes about two minutes. That's fully expected, as the server has to perform an initial sorting for all 50 rows. When I try to remove the index, it takes about one minute. I'm just not What is the reason for an index drop to take that amount of time? All ALTER TABLE operations in mysql are done by rebuilding the table in question and all indexes. You can watch it happen by taking a listing of the database directory and looking at the #sql tempfiles. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grants
Well i think i found a bug or a change un behavior between mysql 5.0.0 ans mysql 5.0.1 The behavior i describe does not happen. Can anybody else confirm this ? Thanks Yann On September 22, 2004 21:51, Yann Larrivée wrote: Hi, I juste installed mysq. 5.1 (alpha) and i guess i am facing some really stupid issue :) I logged in as root without probleme create a database xoops which shows when i do a show databases Now i try to give grants to a user on that datase via grant all on xoops.* to [EMAIL PROTECTED] identified by 'something'; But i keep getting this error ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'xoops' Usually this works well, unless i am too sleepy to see my mistake :) What could be the probleme ? Thanks Yann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie green pea
Hello everyone, I know Rhino gave me a link or something a month ago but. can anyone give me a link to a tutorial on how to ...basically I have been creating my databases one command entry at a time. I just thought if someone knew of a site off the top of there list that could help me create a file of a database and deploy it. I've got a mysql BIBLE, if anything educate me on the terminology so I can look it up. Thanks for anyones generous and helpful response -Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
