Page 1 of 1

mysql version verification

Posted: Wed Oct 18, 2006 10:38 pm
by mckooter
okay i recently tried a new hosting idea for one of my sites, to increase bandwith, in this they advertised mysql4 which is good cause all my scripts were installed for mysql4, now im importing my sql files and running into syntax errors, so before i made a bunch of stupid posts before checking i looked into it, in this i found a few ways to check mysql versions (via google search)

im newer to php and mysql and do only minor work so im not sure if i can trust these scripts word, here they are,

first script, (first one i learned in php):

Code: Select all

<?

phpinfo();

?>
( http://www.mckooter.com/phpinfo.php )

under mysql in that page:
Client API version 3.23.49

SECOND SCRIPT:

Code: Select all

<?php
function find_SQL_Version() {
   $output = shell_exec('mysql -V');
   preg_match('@[0-9]+\.[0-9]+\.[0-9]+@', $output, $version);
   return $version[0];
}

echo 'Your SQL version is ' . find_SQL_Version();
?>
( http://www.mckooter.com/sql.php )
Result:
Your SQL version is 3.23.58
emailed support at this time because their help says they run 4.0.24 and from what i could see they werent, response i got:
(obviously form letter)
We are currently running the 4.0.24 version of MySQL on all of our shared hosting servers. Please follow these directions to import your sql file.
Do i just not know what im looking at or is my server not really running mysql4, i dont want to send them an email telling them they are wrong unless i know for a fact they are, i cant see how it couldnt be but as i stated above im newer,

thanks for reviewing my long message, i like to include all the data so i dont have to reply again and again because i forgot something stupid,

...
Chris

Posted: Wed Oct 18, 2006 11:06 pm
by feyd

Code: Select all

SELECT VERSION();

Posted: Wed Oct 18, 2006 11:16 pm
by mckooter
haha, ive seen that command 5 times tonite but untill you posted it i didnt realize that i needed to put it in as a sql query, ive been trying to run it as a php command, shows my ability


...

okay so apparently were running: 4.0.27-max-log

now im lost then...

this is my error (from a sql file from phpmyadmin on my other server)

Code: Select all

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Oct 18, 2006 at 09:44 PM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
-- 
-- Database: `sportec_silicongrove`
-- 
-- --------------------------------------------------------
-- 
-- Table structure for table `sd_categories`
-- 
CREATE TABLE `sd_categories` (
`categoryid` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`parentid` int( 10 ) unsigned NOT NULL default '0',
`designid` int( 10 ) unsigned NOT NULL default '0',
`name` varchar( 64 ) NOT NULL default '',
`urlname` varchar( 64 ) NOT NULL default '',
`displayorder` int( 10 ) unsigned NOT NULL default '0',
`link` varchar( 128 ) NOT NULL default '',
`image` varchar( 128 ) NOT NULL default '',
`hoverimage` varchar( 128 ) NOT NULL default '',
`menuwidth` smallint( 5 ) unsigned NOT NULL default '0',
PRIMARY KEY ( `categoryid` ) ,
KEY `displayorder` ( `displayorder` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =15;

MySQL said: Documentation
#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=15' at line 14 
if its not the version number im lost...

Posted: Wed Oct 18, 2006 11:33 pm
by Burrito
I don't believe charsets and collations were added until 4.1.

try this:

Code: Select all

CREATE TABLE `sd_categories` (
`categoryid` int( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`parentid` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`designid` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar( 64 ) NOT NULL DEFAULT '',
`urlname` varchar( 64 ) NOT NULL DEFAULT '',
`displayorder` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`link` varchar( 128 ) NOT NULL DEFAULT '',
`image` varchar( 128 ) NOT NULL DEFAULT '',
`hoverimage` varchar( 128 ) NOT NULL DEFAULT '',
`menuwidth` smallint( 5 ) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY ( `categoryid` ) ,
KEY `displayorder` ( `displayorder` )
)

Posted: Wed Oct 18, 2006 11:47 pm
by mckooter

Code: Select all

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Oct 18, 2006 at 09:44 PM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
-- 
-- Database: `sportec_silicongrove`
-- 
-- --------------------------------------------------------
-- 
-- Table structure for table `sd_categories`
-- 
CREATE TABLE `sd_categories` (
`categoryid` int( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`parentid` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`designid` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar( 64 ) NOT NULL DEFAULT '',
`urlname` varchar( 64 ) NOT NULL DEFAULT '',
`displayorder` int( 10 ) UNSIGNED NOT NULL DEFAULT '0',
`link` varchar( 128 ) NOT NULL DEFAULT '',
`image` varchar( 128 ) NOT NULL DEFAULT '',
`hoverimage` varchar( 128 ) NOT NULL DEFAULT '',
`menuwidth` smallint( 5 ) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY ( `categoryid` ) ,
KEY `displayorder` ( `displayorder` )
)--
-- Dumping data for table `sd_categories`
-- 
INSERT INTO `sd_categories`
VALUES ( 1, 0, 14, 'Home', 'home', 1, '', '', '', 47 ) ;

MySQL said: Documentation
#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 'INSERT INTO `sd_categories` VALUES (1, 0, 14, 'Home', 'home', 1
i know im being a putz now, im attempting to learn sql right, it seems as though the number of values to the number of rows is right and that s what i know, this will be the last one i ask i promise, if i dont learn it from this ill look for another host,

Posted: Wed Oct 18, 2006 11:50 pm
by feyd
missing a semicolon after the CREATE TABLE query.

Posted: Thu Oct 19, 2006 6:00 pm
by mckooter
thanks so much

now just a simple question, should i be worried that i dont have mysql4.1+ on this server? i dont want to run into too may problems down the road once i get everything working...

if its somethign that will cause problems i can move again at this point


or if possible is there a quick converter from this or can i force phpmyadmin to output mysql < 4.1 files? or am i manually going through to find and replace these? ... on this file its okay but on my other site its a 10+mb file and would take forever as some of the lines are exact enough for find and replace



ps: haha, i love big companies, in simply asking if they were ever going to update to what obviously seems to be standard now they responded
Current policies prohibit us from discussing any future updates about our products/services that have not been officially released in a press release.

Posted: Thu Oct 19, 2006 6:18 pm
by RobertGonzalez
There are some difference between 4.0 and 4.1+ (and some more between 4.1 and 5+). The biggest is with the password hashing algorithm used by the MySQL password() function. As for compatibility forward, you should have no problems. Going back will be a nightmare.

Others are the date/time functions added to 4.1 and stored procedures/triggers/views/functions added to 5.