executing a sql-statement on a mysql-server fails every-time

sayhello_to_the_world · 08-30-2015, 08:52 AM

first of all - i am not sure if this question is posted in the right forum
i have server - and on this server (which is adminstrated by a friend of me) :: i want to install a software on this server

therefore i have to add a sql file onto the server-db

in earlier times i was running phpmyadmin on the server.
now we have webmin - and i am fairliy new to webmin

note: according to the manual here http://wiki.jobberbase.com/install/guide-for-apache

i want to include a db into the db that i have had created.

question: how to do that - with webmin -

if i add a little snipped - nothing happens - no table were being created.
i have no glue why

is it a permission issue - or what?

Quote:

-- phpMyAdmin SQL Dump
-- version 4.1.9
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Apr 14, 2014 at 09:51 AM
-- Server version: 5.5.34
-- PHP Version: 5.5.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `jobberbase`
--

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

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`id`, `username`, `password`) VALUES
(1, 'admin', '21232f297a57a5a743894a0e4a801fc3');

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`var_name` varchar(32) NOT NULL,
`title` text NOT NULL,
`description` text NOT NULL,
`keywords` text NOT NULL,
`category_order` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_order` (`category_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='programming/design/admin/etc.' AUTO_INCREMENT=15 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `var_name`, `title`, `description`, `keywords`, `category_order`) VALUES
(1, 'Programmers', 'programmers', '', '', '', 7),
(2, 'Designers', 'designers', '', '', '', 3),
(3, 'Administrators', 'administrators', '', '', '', 0),
(7, 'Marketers', 'marketers', '', '', '', 2),
(5, 'Testers', 'testers', '', '', '', 5),
(6, 'Editors', 'editors', '', '', '', 1),
(8, 'Managers', 'managers', '', '', '', 4),
(9, 'Consultants', 'consultants', '', '', '', 6);

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

--

i get back this results:

Quote:

Output from SQL command CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(16) NOT NULL,
`password` varchar(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- --
Dumping data for table `admin` -- INSERT INTO `admin` (`id`, `username`, `password`) VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3'); --
-------------------------------------------------------- -- -- Table structure for table `categories` -- CREATE TABLE `categories`
( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `var_name` varchar(32) NOT NULL, `title` text NOT NULL, `description` text
NOT NULL, `keywords` text NOT NULL, `category_order` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `category_order` (`category_order`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='programming/design/admin/etc.' AUTO_INCREMENT=15

i have no glue why the executing of an sql-statement fains on the webmin

love to hear from you

sayhello_to_the_world · 08-30-2015, 09:39 AM

i get back this results:

Quote:

Output from SQL command CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(16) NOT NULL,
`password` varchar(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; -- --
Dumping data for table `admin` -- INSERT INTO `admin` (`id`, `username`, `password`) VALUES (1, 'admin', '21232f297a57a5a743894a0e4a801fc3'); --
-------------------------------------------------------- -- -- Table structure for table `categories` -- CREATE TABLE `categories`
( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `var_name` varchar(32) NOT NULL, `title` text NOT NULL, `description` text
NOT NULL, `keywords` text NOT NULL, `category_order` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `category_order` (`category_order`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='programming/design/admin/etc.' AUTO_INCREMENT=15

i have no glue why the executing of an sql-statement fains on the webmin

love to hear from you

update

found some more issues - and error messages

if i try to upload the sql-file from the local machine then i get bck the statement below..

what does tis mean?

Quote:

Module Index Execute SQL

Output from uploaded SQL commands ..

ERROR 1064 (42000) at line 613: 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 '38214dc58d7791d92eb96a0a9b2703c80ec38dd922b9a7909ebe1e6437355ede72f8854641287bac' at line 49

ButterflyMelissa · 08-30-2015, 09:48 AM

Quote:

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

It means that there is an error in the SQL command...
Let's dissect...
Help me/us here, edit the post and put the SQL statements in code tags, the quote tags are very helpful, but are not meant to convey code as such

let me try to execute the command on my server here, if I may.
Thor
Edit

Code:

CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(16) NOT NULL,`password` varchar(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2

works nicely...using PHPMyAdmin...
Second edit :

Code:

CREATE TABLE `categories`
( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `var_name` varchar(32) NOT NULL, `title` text NOT NULL, `description` text
NOT NULL, `keywords` text NOT NULL, `category_order` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `category_order` (`category_order`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='programming/design/admin/etc.' AUTO_INCREMENT=15

Umm...works great too...
I'm confused...

sayhello_to_the_world · 08-30-2015, 10:18 AM

hello

many many thanks again for the help. well i struggle with the webmin.
guess that it would work with phpmyadmin too..

btw: if we take a look at the whole installation of the script. then we have some more things left to configure:

see - http://wiki.jobberbase.com/install/guide-for-apache

btw: they want to know the Port
>
>
> // mysql credentials
> 'db_host' => 'localhost',
> 'db_port' => 3306,
> 'db_user' => 'root',
> 'db_password' => '',
>
> ist die 3306 korrekt?

well we do not have SQL over TCP here! we use UNIX-Socket.

this : /home/vhost/WWW/var/run/mysql/mysql.sock

well this might be another story - but what should i add here in the congiguration file

cf see - http://wiki.jobberbase.com/install/guide-for-apache

love to hear from you

NevemTeve · 08-30-2015, 10:28 AM

Shouldn't you guys google for some MySql-quickstart-tutorial?

ButterflyMelissa · 08-30-2015, 11:02 AM

Quote:

Shouldn't you guys google for some MySql-quickstart-tutorial?

LOL, yea, there is an idea...I hope OP has done that...
I just got triggered by the SQL error that was displayed. I tested the SQL and it works.

Quote:

/home/vhost/WWW/var/run/mysql/mysql.sock

@ OP - this worries me. should that be in the home folder?

Quote:

well i struggle with the webmin

as far as I can see, webmin is web based, my thought is that it cannot have an impact...
because...the SQL you posted does work. Is it possible to see line 613, where the error DOES seem to happen? Mostly a quote, a comma or something small is missing...
Thor

sayhello_to_the_world · 08-30-2015, 02:38 PM

hello you both - because of the sockets

we ve got unixsocket on the server that connect to the mysql-db

/home/vhost/WWW/var/run/mysql/mysql.sock

i follow this instrucitons on the server: http://wiki.jobberbase.com/install/guide-for-apache

within the configuration the db port is metioned .
>
>
> // mysql credentials
> 'db_host' => 'localhost',
> 'db_port' => 3306,
> 'db_user' => 'root',
> 'db_password' => '',

my server admin told me that SQL did not run over TCP - here. we allway used and will use
UNIX-Socket

does this mean that i have to do the edit like so

>
> // mysql credentials
> 'db_host' => 'localhost',
> 'db_port' => '/home/vhost/WWW/var/run/mysql/mysql.sock,
> 'db_user' => 'root',
> 'db_password' => '',

love to hear from you

note: i follow this instrucitons on the server: http://wiki.jobberbase.com/install/guide-for-apache

ButterflyMelissa · 08-31-2015, 09:58 AM

Quote:

db_port' => '/home/vhost/WWW/var/run/mysql/mysql.sock,

No, this is not a port, a number is required here...if this setup was not altered (too much) the original port still is 3306...
Of course, I use mySQL completely different. I did a port scan and this port is not open, but...I approach the data via my own components...I have no need to open a port outside anyway...
Thor

sayhello_to_the_world · 08-31-2015, 07:56 PM

dear Thor

many many thanks for all the help. GREAT to see all your ideas & help here in the forums.

topic solved - now all works like a charme

thx for all the help

ButterflyMelissa · 09-01-2015, 08:39 AM

Good to hear
Mark the thread as solved after you posted the solution that worked for you...for the users in the future that may stuggle with a similar challenge

Thor

TB0ne · 09-08-2015, 03:09 PM

Quote:

Originally Posted by Thor_2.0

Good to hear
Mark the thread as solved after you posted the solution that worked for you...for the users in the future that may stuggle with a similar challenge

Thor

Good luck with that, Thor_2.0. Please see the OP's posting history.