mysql version verification

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mckooter
Forum Commoner
Posts: 26
Joined: Fri Jul 28, 2006 10:02 pm

mysql version verification

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT VERSION();
mckooter
Forum Commoner
Posts: 26
Joined: Fri Jul 28, 2006 10:02 pm

Post 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...
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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` )
)
mckooter
Forum Commoner
Posts: 26
Joined: Fri Jul 28, 2006 10:02 pm

Post 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,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

missing a semicolon after the CREATE TABLE query.
mckooter
Forum Commoner
Posts: 26
Joined: Fri Jul 28, 2006 10:02 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply