Why won't PHPMyAdmin alter my tables??

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Why won't PHPMyAdmin alter my tables??

Postby FrankC » 14. May 2013 17:59

I made a table in my local PHPMyAdmin. I can do all sorts of things with it, but I cannot make even the slightest alteration. For example, under the tab Operations, there is the function Alter Table Order By. But even that it won't do. I can select a field, select Ascending or Descending, click Go, and the system will even report back that the query has been executed successfully. But when I subsequently browse the table, nothing has changed. No matter which field I select.

The alternative method doesn't work either. It will let me hustle the table all I want, as long as it goes via the command

SELECT * FROM `table_name` ORDER BY `field_name` .

But when I enter

ALTER TABLE `table_name` ORDER BY `field_name`

the table doesn't change one iota, while I do get the message that the query has been executed successfully.

What's up with that??

----------------------------------------------------

Edit: I got an answer already, via Stackoverflow.com. See here for the answer: http://stackoverflow.com/questions/1654 ... -my-tables. This behavior is standard as of MySQL 5.5, but XAMPP/PHPMyAdmin seemingly has not been updated yet in that respect.
FrankC
 
Posts: 16
Joined: 23. October 2011 03:57
Location: Amsterdam, The Netherlands
XAMPP Version: 1.8.3-3
Operating System: Windows Vista Basic

Re: Why won't PHPMyAdmin alter my tables??

Postby Nobbie » 14. May 2013 20:02

FrankC wrote:the table doesn't change one iota, while I do get the message that the query has been executed successfully.

What's up with that??


The problem is not on MySQL, the problem is on yourself. What did you expect, what should happen after executing the "ALTER" command?

If you have a table, and want to see the contents of that table, you have to run a "SELECT" command. If you do not supply any "ORDER" clause, the table order (shown by this SELECT) is the order of the primary key.

If you apply an ORDER clause "SELECT * ... ORDER BY field_name", this will result in a table, which is ordered by field_name.

In any case, the ORDER of the table is NOT affected by an "ALTER" clause, it is only effected by the SELECT ... ORDER statement.

So, what does it mean to run a "ALTER ... ORDER by field_name"? As stated above, this does not have any effect on subsequent SELECTs. So this seems to be a stupid statement. But - it is not. The problem ist not the ORDER itself, the problem is execution time. Per default, all tables are (internally) ordered by the primary key. Therefore, if you run a "SELECT .... ORDER BY prim_key", this SELECT is executed very very fast, because the whole table is already ordered by the Primary Key and there is no extra work to be done for ordering this table.

But if you run "SELECT ... ORDER by field_name" instead, MySQL has to re-order all data what might take a certain amount of time. And this is the idea of using "ALTER": in that case, the table is physically NOT ordered by the primary key, but by the field_name. And if you run a SELECT .... ORDER by field_name after that, MySQL does not need to re-order the table, as it is already ordered in that way.

BUT: the result of ANY SELECT is ALWAYS the same (this is what makes you believing, that ALTER is not working). But the execution time can vary extremeley, dependig on the size of the table and depending on the default physically ORDER.

Therefore, you cannot see any difference before/after running this ALTER statement. But if you run benchmarks and have large amount of data, you will see big differences in execution time.
Nobbie
 
Posts: 6578
Joined: 09. March 2008 13:04

Re: Why won't PHPMyAdmin alter my tables??

Postby FrankC » 15. May 2013 15:15

@Nobbie:

Your answer is incorrect, because after removing the primary key, the ALTER did work. It's just a matter of the change to default InnoDB as per MySQL 5.5 that caused the problem. Which can be solved by removing the primary key or changing the database engine back to MyISAM.

But PHPMyAdmin (not XAMPP) should give a warning that ALTER does not work on InnoDB databases when tables have a primary key. It should certainly not report back (anymore) that such a query has been executed successfully.
FrankC
 
Posts: 16
Joined: 23. October 2011 03:57
Location: Amsterdam, The Netherlands
XAMPP Version: 1.8.3-3
Operating System: Windows Vista Basic

Re: Why won't PHPMyAdmin alter my tables??

Postby Nobbie » 15. May 2013 16:14

FrankC wrote:@Nobbie:

Your answer is incorrect, because after removing the primary key, the ALTER did work.


This is right, if you have no primary key. But is stupid to have SQL without primary key.

FrankC wrote:It's just a matter of the change to default InnoDB as per MySQL 5.5 that caused the problem. Which can be solved by removing the primary key or changing the database engine back to MyISAM.


No, this is a basically wrong way of programming. Your programs MUST NOT rely on the kind of used database engine; i.e. all selects HAVE TO BEHAVE THE SAME WAY, not depending on innoDB or MyISAM.

FrankC wrote:But PHPMyAdmin (not XAMPP) should give a warning that ALTER does not work on InnoDB databases when tables have a primary key. It should certainly not report back (anymore) that such a query has been executed successfully.


See above - it is completely stupid to use SQL without primary key. What is the idea of a database without primary key? You could use simple text files instead.

Anyway, ALTER .... ORDER BY does not affect the design of the data (what is most important to know). Your programs *MUST* apply ORDER BY to all SELECTS if a certain ORDER is expected.
Nobbie
 
Posts: 6578
Joined: 09. March 2008 13:04

Re: Why won't PHPMyAdmin alter my tables??

Postby Altrea » 15. May 2013 16:57

FrankC wrote:But PHPMyAdmin (not XAMPP) should give a warning that ALTER does not work on InnoDB databases when tables have a primary key.

:?: :?:

ALTER TABLE works as expected
Code: Select all
CREATE TABLE IF NOT EXISTS `innodb_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` smallint(6) NOT NULL,
  `col2` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `innodb_test` (`id`, `col1`, `col2`) VALUES
(1, 0, 'def\r\nghi'),
(2, 0, 'mno\r\noqr'),
(3, 123, 'abc\r\ndef'),
(4, 456, 'ghi\r\njkl');

ALTER TABLE `innodb_test` MODIFY `col1` BIGINT;


And what the MySQL Documentation is telling is so clear:
http://dev.mysql.com/doc/refman/5.5/en/alter-table.html wrote:ORDER BY does not make sense for InnoDB tables that contain a user-defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB always orders table rows according to such an index if one is present.

So for MySQL the SQL Statement was executed successful. The result is as expected.
If you want to use ALTER TABLE in a way it is not supported by the Engine of the DBMS... what else should we answer!?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 6536
Joined: 17. August 2009 13:05
XAMPP Version: 5.5.19
Operating System: W7Ux64

Re: Why won't PHPMyAdmin alter my tables??

Postby FrankC » 16. May 2013 09:51

Nobbie wrote:See above - it is completely stupid to use SQL without primary key. What is the idea of a database without primary key? You could use simple text files instead.

That is the biggest baloney I have ever read. As if you can't retrieve one record from a table that has no primary key. And as if you can load data into a statistics program from a text file.

Altrea wrote:So for MySQL the SQL Statement was executed successful. The result is as expected.

No, it isn't. If I ask to alter the table so that the rows are alphabetically sorted by a certain field with text values, ask that by means of a direct MySQL command, and the system reports back that the query has been executed successfully but the table has not actually changed one iota, then the result is *not* as expected. You can twist and turn all you want, that is the plain and simple truth. Every reasonable person will agree to that.

Your reasoning compares to filing a complaint with the police at the police station, the police station later telling you that the complaint has been handled, but still later it appears that the department that should have executed the matter has done nothing at all with the complaint. You would go mad.

Don't put your credibility at risk by trying to twist-turn-talk you way out of it.
Last edited by FrankC on 16. May 2013 17:56, edited 1 time in total.
FrankC
 
Posts: 16
Joined: 23. October 2011 03:57
Location: Amsterdam, The Netherlands
XAMPP Version: 1.8.3-3
Operating System: Windows Vista Basic

Re: Why won't PHPMyAdmin alter my tables??

Postby Altrea » 16. May 2013 17:51

If you want to use a banana to knock in a nail into a stone wall, your expected result is a knocked in nail in a stone wall? :shock:
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 6536
Joined: 17. August 2009 13:05
XAMPP Version: 5.5.19
Operating System: W7Ux64

Re: Why won't PHPMyAdmin alter my tables??

Postby FrankC » 16. May 2013 17:58

Altrea wrote:If you want to use a banana to knock in a nail into a stone wall, your expected result is a knocked in nail in a stone wall? :shock:

A comparison that doesn't make any sense whatsoever. It's a pity to see that not having to admit is more important to you than your credibility and reputation of being a reasonable person.
FrankC
 
Posts: 16
Joined: 23. October 2011 03:57
Location: Amsterdam, The Netherlands
XAMPP Version: 1.8.3-3
Operating System: Windows Vista Basic

Re: Why won't PHPMyAdmin alter my tables??

Postby Altrea » 16. May 2013 18:14

FrankC wrote:A comparison that doesn't make any sense whatsoever.

=> you are using the wrong tool relating to your expectation. I think the comparison makes perfect sense.

FrankC wrote:It's a pity to see that not having to admit is more important to you than your credibility and reputation of being a reasonable person.

I simply don't care what anybody is thinking about me.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 6536
Joined: 17. August 2009 13:05
XAMPP Version: 5.5.19
Operating System: W7Ux64

Re: Why won't PHPMyAdmin alter my tables??

Postby FrankC » 17. May 2013 14:02

Altrea wrote:you are using the wrong tool relating to your expectation.

A direct and valid MySQL script line, which does work if the primary key is removed, is using the wrong tool to change the order of a MySQL table???
FrankC
 
Posts: 16
Joined: 23. October 2011 03:57
Location: Amsterdam, The Netherlands
XAMPP Version: 1.8.3-3
Operating System: Windows Vista Basic

Re: Why won't PHPMyAdmin alter my tables??

Postby Altrea » 17. May 2013 14:13

Don't feed the trolls 8)
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 6536
Joined: 17. August 2009 13:05
XAMPP Version: 5.5.19
Operating System: W7Ux64


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 50 guests