Invalid Index
I try to used the MyPhpAdmin 2.8.2.4 with PHP 4.4.4, MySQL5.0.2.4 and Apcache2 and I got this error, 'invalid Index'
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Warning: Mysql_data_seek(): Offset 0 Is Invalid For MySQL Result Index 2
I only posted this on the MySQL forums becouse i believed it had to do with the query. I get the following error: Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 2 (or the query data is unbuffered) mysql_data_seek is used in a function (made not by me) which i know 500% that it works for other queries. The query in PHP is this:
Invalid Use
I am having a problem with group. Following is my query but it gives an error of "Invalid use of group". SELECT refMember.MemberAcroName, refMember.MemberName, Count(DistrictLevelInformation.FormID) AS Spread FROM (refMember LEFT JOIN MainInformation ON refMember.MemberID = MainInformation.MemberID) LEFT JOIN DistrictLevelInformation ON MainInformation.ID = DistrictLevelInformation.FormID GROUP BY refMember.MemberAcroName, refMember.MemberName, MainInformation.qYear, MainInformation.Quarter ORDER BY MainInformation.qYear DESC , MainInformation.Quarter DESC , Count(DistrictLevelInformation.FormID) DESC
Invalid Use
I have tested my query on MySQL 5 and it works, sadly the server it is to be run on only has 3. I was just wondering what I need to update in the following query to get it working on 3 or if it is even possible to get it running on 3. Code: ..... and keyword IN ('keywords') group by people having count(*) >= 1 ORDER BY people ASC
Invalid Use Of Group By
I have the following query: SELECT CLAIMPOLICYNUMBER AS Plan, PROVIDERSPECIALTY AS Specialty, TINNUM AS Tin, SUBTINSEQNUM AS SibTin, CHARGETYPECATEGORY AS ChargeCategory, SUM(BILLEDCHARGE) AS Billed, SUM(PAID) AS Paid, " + _ COUNT(DOCUMENTNUM) AS ChgCount FROM BAT WHERE CASENUM='0000911' AND CHARGETYPE <> 'DENTAL' AND CHARGETYPE <> 'VISION' AND CHARGETYPE <> 'ADMIN' AND PMTDATE >= '2005-06-01' AND PMTDATE <= '2005-06-30' GROUP BY CLAIMPOLICYNUMBER, PROVIDERSPECIALTY, TINNUM, SUBTINSEQNUM, CHARGETYPECATEGORY ORDER BY SUM(BILLEDCHARGE) It works great without the ORDER BY clause, but with it, I get the aforementioned error.
Directory Name Invalid
For testing purposes I have PHP installed and I have MySQL installed on my local laptop. Windows XP Pro I am just learning both of them. I have read quite a few chapters in the MySQL Manual. Enough to allow me to get around in MySQL and directly create tables an view information in them, upload .txt files into the tables, etc etc. I am receiving an error when I try to connect to MySQL via a PHP script. I have no problem connecting to MySQL via Command Line. The error I receive is "The directory name is invalid." Here is my code... I am not sure where to put the directory name or where to put the directory path in order to make it valid. I am not sure what directory it is talking about. <html> <head><title>Connect to MySQL</title></head> <body> <?php //connecting to the mysql server uses a mysql_connect command. $link = mysql_connect("locoalhost",$_POST['username'],$_POST['password']) or die("Connect Error: ", . mysql_error()); echo 'Successfully Connected. '; mysql_close($link); ?> </body> </html> Where exactly would I put the path to the directory or where would I name the directory?
Invalid Syntax
I am having trouble changing my password back to OLD_PASSWORD by the following : mysql> SET PASSWORD FOR -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd'); When I do it, it says ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'OLD_PASSWORD ('newpwd')' at line 2
Invalid Use Of Group Function
I have the following Sql Statement... I'd like to find the sum of hrs worked by an employee in the month, the required number of hrs, and the sum of the difference between the two, the worked hrs are calculated by subtracting the max vtranstime - min vtranstime and the required hrs are calculated by subtracting max to1 - max from1.... I tried to do the following: select sum(timediff(max(vTransTime),min(vTransTime))) as worked, sum(timediff(max(translog.to1),min(translog.from1))) as required, sum(timediff(sum(timediff(max(vTransTime),min(vTransTime))), sum(timediff(max(translog.to1),min(translog.from1))))) as overtime, empid,deptname, firstname,lastname from employee,translog,department where employee.emp_magnetic_num=translog.vId group by employee.empid I am getting the following error : Invalid use of group function
Invalid Backup File
I am tring to restore a .pmb backup file and getting the message "Invalid backup file". I am very new to myssql so I don't have a clue what that means. Wrong version of mysql? Corrupted file?
Invalid Use Of Group Function
Using mysql 4.0 I get the following error message "Invalid Use of group function" with the following code: update custRP c, tbl_acctbal a set c.rpBal = sum(a.currRPBAL) where c.custid = a.custid
Invalid Argument While Using BCP (Script)
This will be a 50/50 question. It's either something that the SQL experts can answer or a scripting forum. I'm new to SQL and just coming to grips with getting familiar with scripts. I've developed a small script for my workplace that will connect to a remote server and extract the specified table that I want, zip it up and then copy it back to my local machine. This works fine until it tries to run the bcp command. If I tivoli to the remote machine and type the bcp command in command prompt it works fine. However when executed from the script it throws an invalid argument and seems to refer to the "-" minus I place in front of the U (user) or P(pwd). The script echos the users table request to a file which is then copied to the remote server (SQLtable.cmd) and then PSEXEC is called to connect to the server and run the script. e.g. psexec \%Server% -u %user% -p %password% "C: empSQLtable.cmd" PSEXEC then returns the following error. C:WINNTSYSTEM32>Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat ûUsa ûPpwd (chopped off the -n) Unknown argument '¹Usa' on command line. Sorry about all the data, just hope someone can help. Must be an easy way. The small script which psexec is executing contains two simple lines. Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat -Usa -Ppwd -n wwzip -9 C: emp\%username%SQLtables.zip C: emp\%username%*.*
Invalid Characters With PHP And MySQL
I am writing about a situation that our client is currently experiencing with Linux Mandrake, Apache, MySQL and PHP. We successfully deployed a website running on this type of system for a period close to two years. It has a HTML GUI component that the client can type or paste content into and it serves-up dynamic pages in PHP from a MySQL database. Recently the web site was hacked by the Travel Bug virus and it forced the client to restore the web site and server software mentioned below. Original Install Mandrake 8, MySQL 3.23.32, PHP 4.0.06 and Apache 1.3.26-x.x Current Install Mandrake 9.0, MySQL 3.23.52-1, PHP 4.2.3-1.1 and Apache 1.3.26-6.1 The current install has lead to new problems. Content with double and single quotes are showing up as squares or question marks. For example, I have a page that is showing the square symbol in place of double quotes. If I do a view source on that specific page I can see the double quotes but if I telnet into the server and query the MySQL record belonging to that page the square or double quote character is nowhere to found. The following items are the steps we tried to understand why this is happening. First, we did the following to test the integrity of the MySQL backup file. We Restored the MySQL database on our test environment which has the original software specs mentioned above but on a window server running IIS not apache. I did a restore of the MySQL database file and did not get any invalid characters. Even though the environment isn’t completely identical to the clients we can make the assumption that the dump file wasn’t corrupted. Second, possibly content editors were copying and pasting from word into the HTML GUI component thus creating these invalid characters. In most cases yes but I personally noticed the invalid character issue with a page that doesn’t use the HTML GUI component. Third, Possibly the configuration especially the character set from the original version to the current version of MySQL is not configured correctly. We believe this to be the primary issue. The resolution is to configure or reinstall MySQL and restore from backup. Fourth, has anyone tried the PHP functions to convert the invalid characters. A request was to look into the iconv() function. The iconv library functions convert strings between various character sets encodings. This involves compiling the PHP interpreter which is why I haven’t tested it. Fifth, Can some other process running on the server be causing this? Altogether, we soon will begin creating a test server identical to the original settings and step through the install and try to recreate the invalid character issue. But, if anyone has experienced this invalid character issue or has more information that would benefit us your help would greatly be appreciated.
Invalid Use Of Group Function - Max(`mainid`)+1
I am trying to insert a incremented number into the menu table. I will have an autoincremented number as well as the mainid number. When I tested the code that is below, the above error happened. Can someone please tell me the correct way of doing this? I am getting really frustrated with this. Nothing wants to work for me. require "config.php"; $insert06 = "INSERT INTO `menu` (`mainid`) VALUES (max(`mainid`)+1)"; // error line if (mysql_query ($insert06)) { print "Mainid added."; $query2 = mysql_query("SELECT mainid FROM menu") or die ("Could not query because: ".mysql_error()); while($row2 = mysql_fetch_array($query2)) { echo $row2['mainid']." = Mainid"; } } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $insert06.</p>"; } mysql_close();
Group By Returns Invalid Dates
Hi i have one table: ID, User, Score, Date 1, mac, 1234, 2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57 3, mac, 999999, 2007-11-07 09:44:14 I wan to create a high score list which will represent the best scores but not repeating anyone for example: 1, mac, 999999, 2007-11-07 09:44:14 2,kurca, 3232, 2007-12-05 09:43:57 Now i use: SELECT ID, User,MAX(Score) as maxScore,Date FROM `table` GROUP BY User ORDER BY maxScore DESC with this query the score is right but the date is invalid example: 1, mac, 999999,2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57
Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id "; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?
Error:invalid Use Of Group Function
version:mysql 4.0.18 for win hi,all dear:) when execute sql-statement, error raise: update customer,cu_order set customer_point=0 where customer_keyid=order_customerid and DATE_SUB(now(),INTERVAL 1 YEAR)=DATE_FORMAT(max(order_time),"%Y-%m-%d 23:59:59") error:invalid use of group function i beg someone tells me why?how resolve? thx
Valid/Invalid Column Names
I'd like to present tabular data in HTML with the first row being the names of the columns in my database. However, I'd like them to be more presentable; for example 'I.P. Address' instead of 'ip_address'. I was wondering if a name like 'I.P. Address' could ever somehow be a valid column name or not.
Invalid MySQL-Link Resource
I'm getting the error message 'Supplied argument is not a valid MySQL-Link resource' (applies to the 3rd line of code) when the code below executes: $delete_part_no_query = "DELETE FROM prices WHERE part_no == '$part_no_to_delete'"; $delete_part_no_result = mysql_query($delete_part_no_query); $num_delete_part_no_result = mysql_affected_rows($delete_part_no_result); The query works fine and I'm under the impression that 'mysql_affected_rows' should be used to return the number of rows affected by DELETE. However, I keep getting the above error message.
Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column. For close to two years, this query has worked perfectly fine: SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values. This query would usually return: Code:
Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on... column 1, column 2, and column 3 If we do a select statement matching only on column 2 and column 3 will the index still be used effectively? Do we then need a second index only on column 2, and column 3? Would it be smart to simply create indexes on all of the following? column 1 column 2 column 3 column 1, 2, 3 column 2, 3 ?
Howto Add Another Index To A Default Index
I have a index rang of 1-5 set as default. I am now trying to add an index 6, i know it sounds easier enough but i cannot come right. Let me past the code segment below to get a better idea of what is happening: Code:
Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins ERROR: #1111 - Invalid use of group function
Performance Of UNIQUE Index Compared To "normal" INDEX
I have a table with several columns. one column "myColumn" of this table has a UNIQUE index on it. i want to use this column in many many search queries. does it make sense - in terms of performance - to add an additional INDEX to "myColumn"? or does an UNIQUE index already "include" a "normal" INDEX?
What Should I Index In This?
If this is my query and I'm calculating distance between users what should I index in the database? This query works really fast in my database of 10,000 users when I have state='xx' in the where clause. But if I don't it goes crazy and takes minutes! I'd love to sort by distance but that takes too long as well. Any ideas on what to index? SELECT SQL_CALC_FOUND_ROWS users_info.username,users_info.id ,CONCAT(zipcodes.city,', ', zipcodes.state) AS user_area ,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthdate)), '%Y')+0 AS user_age ,user_greeting.greeting , (3958 * 3.1415926 * sqrt((zipcodes.latitude - -111.978898) * (zipcodes.latitude - -111.978898) + cos(zipcodes.latitude / 57.29578) * cos(-111.978898 / 57.29578) * (zipcodes.longitude - +40.606125) * (zipcodes.longitude - +40.606125)) / 180) as distance FROM users_info LEFT JOIN user_greeting ON users_info.id = user_greeting.user_id INNER JOIN zipcodes ON users_info.zip = zipcodes.zipcode WHERE users_info.birthdate<��-02-16 06:34:03' AND users_info.birthdate>��-02-18 06:34:04' AND users_info.state='CA' AND users_info.sex = Ƈ' HAVING distance<ཕ' LIMIT 0,10
Should I Use Index?
I have table Files(id,name,type) . Type is INT, the possible values are(1,2,3,4,5). Often i execute query which gives me files of given type e.g.: SELECT id,name FROM files where type = 1
Why LIKE Not Using INDEX?
I have a table with an intger column called ID. I have an index on it. Now I want to get a result with all the rows whos ID values start with lets say 12....i.e. I want all the IDs with data - 12 123 1234 ........... select * from tablename where id like '123%' It is returning me correct results...but the query does not seem to be using the INDEX? My question is ... does MySQL not uses INDEX when we execute a LIKE command on a NUMERIC data.... __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw
Index
I read that it's preferable to shorten the length of an index if it's based on a varchar. But if I shorten a column defined as: name varchar(40) with an index of: index ix_name (name(10)) and then I try to do an insert into the table a "name" longer than 10 (but less than 40) the insert will fail with the error message "data too long for column name in row XXX". what am i doing wrong? (mysql 5.1)
Index
Not sure how indexes work with ORDER BY and GROUP BY. What index should I create which will be used on SELECT '' As ID, `Source Presidency` As Presidency, `Source Name` As Name, MIN(`Source Year`) As `Start Date`, MAX(`Source Year`) As `End Date` FROM burialdeathfull GROUP BY `Source Presidency`, `Source Name` ORDER BY `Source Presidency`, `Start Date` Everything I ttry doesn't work.
2 Index
i've been looking at some of my old databases with a view to optimising them as i'm totally self-taught and never realised loads of stuff like indexing when i created the databases! i'm using MySQL 3.23.58, all tables using MyISAM. on one of my tables (orders) i have built an index on the customer ID field but when i view it in phpMyAdmin this index has the same cardinality as the PRIMARY index (i.e. order number). now i know for sure that some customers have ordered more than once, so shouldn't the cardinality of the customer INDEX be lower than the PRIMARY INDEX? the customer ID field is type MEDIUMINT(8). on another database, in another table, i have 4 text columns for varying degrees of scientific calculations (species, genus etc). i have built a FULLTEXT index across the 4 columns as i want non-scientific people (who don't understand the difference between genus, species etc) to be able to query the database. now i don't seem to be able to add any other INDEXes on this table. i want to index the genus column by it's first letter so i can browse the table by letter. this works fine without the FULLTEXT INDEX but not with. is this right that a FULLTEXT INDEX can be the only INDEX (other than the PRIMARY) on a table.
Index To Hit First
I have a multi-million row table with three indexes in MySQL-5.0.15. These indexes have the following number of distinct values: date 415 block 100000 scan 45 If I'm doing a query on this table in what order should I hit these indexes? The largest first, to get rid of the most possibilities right away? Or the smallest first, because it has the fewest number of rows to scan to make the cut? At some level both seem to make sense, but I'm doing smallest to largest. Is this the most efficient? Or is this a case of just letting the query optimizer choose? I don't yet have a compound index on these fields.
Index Is Not Used :-(
It is a simple query and my index is not used :-( Index is "zadnj"e and I indexed "zadnjidatum, zadnjiuporabnik" together. Query is EXPLAIN SELECT r.ID, r.imerecepta, r.zadnjidatum, r.zadnjiuporabnik, r.stmnenj, o.ime, o.uporabnik FROM recepti r INNER JOIN obiskovalci o ON r.zadnjiuporabnik = o.ID ORDER BY r.zadnjidatum, r.zadnjiuporabnik Explain says: id select_type table type possible_keys key key_len ref rows Extra 1SIMPLErALLzadnjeNULLNULLNULL6788Using filesort 1SIMPLEoeq_refPRIMARYPRIMARY4r.zadnjiuporabnik1 What am I doing wrong? I used another similar query and I didn't get using filesort and not using index.
Using Index
I had a 1.6 Gb table (innodb) with 12 indexes, which I later reduced into a 250Mb table with 2 indexes. The problem is that the index seems to be bigger in size than the table (270Mb). What am I doing wrong? How can I make the index smaller?
Why Don't Use Index
I have a table Test with three fields: id int(11) not null auto_increment; name varchar(15) not null; age tinyint(4) not null. [1]select * from Test where id like '%1'; [2]select * from Test where id like '1%'; It's said that query[1] cannot use index, but query[2] can do. But with my tests, both of them didn't use index. My tests were very easy, I used "log_queries_not_using_indexes" option. The relevant statements in my.ini as follows, log-slow-queries=slow-query.log long_query_time=10 log_queries_not_using_indexes I just had a little data, so both queries weren't "long time query".
Index
what is index in mysql Action next to PK.
WHERE..IN Not Using Index
Here is the query: SELECT `vkey`, `data` FROM `registry` WHERE `vkey` IN('prioritycache','statuscache','staffcache','departmentcache','slaplancache','slaschedulecache', 'escalationrulecache','attachmenttypescache','ticketcountcache','ticketgridcache','ticketviewcache', 'queuecache','instaalertcache','ticketlabelcache','labelcountcache','gridcache','groupsettingcache', 'commentcache','staffassigncache','groupassigncache','tgroupassigncache','settingscache','tgroupcache','languagecache'); An INDEX exists on the `vkey` column. I've already OPTIMIZE, and ANALYZE this table. But when I run the query with EXPLAIN, no indexs are used. There are 58 rows in this table. If I reduce the number of options in the IN portion down to like, 5 or 6, the index is used. But if you increase past 5 or 6, the INDEX is no longer used.
INDEX Became KEY
i've created table manually and set INDEX on the one of my columns, then i exported my table with phpmyadmin and the INDEX became KEY in the output. are they similar?
Index
How to know if a column fileds are distinct or not ?
Index
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes. Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.
Getting The Index
I've got a table with an auto-incrementing index column (named "ID") which is also the primary key. After inserting a new record, is there an efficient way to get the index at which it was inserted? Presumably I could do "select max(id) from tablename", or "select ID from tablename order by ID desc limit 1". (Which one would be faster, BTW?) But that's assuming that no other records are inserted between the insert and the query. Is there a common method of doing this?
Index Help
I deleted a fulltext index on one of my mysql tables, which then broke my php frontend. I recreated the index, but the app is still broken. How can making a change to a secondary index, or in this case a fulltext index, adversly affect my application?
Index
Do I have set index (using KEY) on a field which is already indexed literly (using FULLTEXT INDEX)?
Index 7
The following is part of my script that does not work, the first line is what i think is causing the problem. going by the error returned. i have had this before and looked at my other script to work it out again, but can not see anything wrong $res = mysql_query("SELECT * FROM list WHERE email='$findstring' LIMIT 1") or die(mysql_error()); $name = mysql_result($res, 0, "name") or die(mysql_error());
An Index
I've a mysql database running with a lot of documentation files. Now I wonder how it is possible to generate an index file (in plain text) of this files sorted by description for publishing on my website.
Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things. In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches. Is there such a feature or tool for MySQL?
Delete And Re-index SQL
Out of interest -If I delete records from within the db are the records stored in a contiguous fashion or are there "gaps" in the table ? How do I go about deleting records from within a db and then RE-INDEX so that the records have new index id numbers?
|
TRACKER
