Problems importing a Mariadb Database
Posted by: markoroots (---.cust.vodafonedsl.it)
Date: July 27, 2017 07:45PM

Hi everybody,
I have installed the program, loaded the ftp folder, created a database and the user to enter.
Now that I try to copy the db on mysql folder by the this code at command prompt: source (and file path)
it comes only errors 1146
that say all: sefb.vb4_contentthread doesn't exist

"sefb" is the name that I gave to the db on the pc and vb_4 is the prefix of the online db (of Vbulletin) that I have copied.

Please can someone help me?



Edited 1 time(s). Last edit at 07/27/2017 09:07PM by markoroots.

Options: ReplyQuote
Re: Many Error 1146 can't load database
Posted by: Otomatic (Moderator)
Date: July 27, 2017 07:54PM

Hi,

> I have installed the program
What program?
Please answer all the questions of READ (and answer) BEFORE YOU ASK A QUESTION

> Now that I try to copy the db on mysql folder
COPY ? How did you do that ?

The transfer of databases between different versions of MySQL or MariaDB should be performed by EXPORT / IMPORT of SQL files using phpMyAdmin or a similiar tools of your choice.
Do not copy the data folder between different versions of MYSQL. Databases from different versions of MYSQL are VERY unlikely to be compatible! You will also destroy the version of MYSQL that you copied the data folder to.

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Many Error 1146 can't load database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 27, 2017 09:06PM

Ah ok I didn't see it before.
The problem was that I was try to import a Mariadb database on Mysql.
Now I have installed the Mariadb upgrade and I try to re-import the db again. This time the process start, but only in the end show me some errors the I as shown in the details:


1 - Windows version used (Specify version number AND whether 32 or 64 bit) 7 32bit
2 - Version WampServer (Specify version number AND whether 32 or 64 bit) 3.0.9
3 - Apache Version 2.4.23
4 - PHP Version 5.6.25
5 - MySQL Version 5.7.14
6 - What color is the WampServer icon (in the notification area of the taskbar) Green
7 - In the file c:\Windows\System32\drivers\etc\hosts, what are the active lines with the name [localhost] in them? No one. I see only host in the name of the files.
* Active means no # at the beginning of the line.

WampServer icon is green:

8 - Do you have access to localhost (Homepage WampServer)? Yes
8a - If so, is there an error message at the bottom of the page? No

9 - Do you have access to phpMyAdmin? Yes

10 - If you refer to an error message, please include the EXACT wording of the error in your post:
ERROR 1231 (42000) at line 15940 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'time_zone' can
't be set to the value of 'NULL'
ERROR 1231 (42000) at line 15942 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'sql_mode' can'
t be set to the value of 'NULL'
ERROR 1231 (42000) at line 15943 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'foreign_key_ch
ecks' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 15944 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'unique_checks'
can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 15945 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'character_set_
client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.00 sec)

ERROR 1231 (42000) at line 15947 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'collation_conn
ection' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 15948 in file: 'C:\Users\Alberto\Desktop\Server compl
eto 23 07 2017\database\ro756\ro756tt_db2.sql': Variable 'sql_notes' can
't be set to the value of 'NULL'
MariaDB [sefb]>

11 - If you use an Antivirus and / or Firewall, please include the names of these. Avast, Spybot and Window Firewall.
12 - What is the full path to the installation of WampServer (eg D:\wamp\) c:\Wamp
13 - If you have one or more Virtual Hosts defined, please show their definition(s). I haven't.



Edited 2 time(s). Last edit at 07/27/2017 09:31PM by markoroots.

Options: ReplyQuote
Re: Many Error 1146 can't load database
Posted by: Otomatic (Moderator)
Date: July 27, 2017 09:31PM

Hi,

In the c:\Windows\System32\drivers\etc\hosts file write only:
127.0.0.1 localhost
::1 localhost

In Mysql try :
Left-Click Wampmanager icon -> MySQL -> MySQL settings -> sql-mode -> sql-mode->none

All your errors are not from Wampserver or from MySQL but are in your sql file.
Several variables used in your SQL file have the value NULL which is not allowed by MySQL.

See what it is written in your SQL file from line 15940 to line 15950

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 09:02AM

Hi Otomatic, many thanks for the support.
The problem is that I'm using Mariadb and not Mysql.
So have to modify Mysql as you said? And if yes where I found the Mysql to follow your instructions?

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 09:07AM

And also in the host file, have I delete all and add the lines of code that you give me or have I to add the code and let the rest. Because there are many voices created by Spybot Search and Destroy I think to immunize the system.
I have also seen that the system don't let me save the "host" file.



Edited 1 time(s). Last edit at 07/28/2017 09:10AM by markoroots.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: Otomatic (Moderator)
Date: July 28, 2017 09:38AM

Hi,

In MariaDB there is also a sql-mode but, for the moment, it is not (yet) directly supported by the menus of Wampmanager.

In the file wamp\bin\mariadb\mariadb10.2.x\my.ini before :

; The default storage engine that will be used when create new tables

insert :

; Set the SQL mode
sql-mode=""

The hosts file is not intended to perform url filtering as Spybot Search and Destroy does.
In my humble opinion, it is a trick to uninstall completely; It is an unnecessary tools that poses many more problems than it solves.

Second request:: what it is written in your SQL file from line 15940 to line 15950

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 10:36AM

Many thanks.
I have corrected the my.ini file as you tell me in this way:
;Path to the language
;See Documentation:
; [mariadb.com]
lc-messages-dir="c:/wamp/bin/mariadb/mariadb5.5.54/share"
lc-messages=en_US
; Set the SQL mode
sql-mode=""
; The default storage engine that will be used when create new tables
default-storage-engine=MYISAM

; Point the following paths to different dedicated disks
;tmpdir = /tmp/


Do you mean that I have to unistall Spybot? But this is a software that I have from years and always discover many troians that others softwares don't see, so for this I'm really fond to this program.

I'm waiting that the db file come opened. I will keep you updated when ready.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 12:43PM

Also I think is important that you know that when I have installed the forum many years ago I had Mysql but I don't remember the codify (maybe Myisam). Now that I migrate the forum to Mariadb the db codify is Innodb.
Could be this the problem?

I'm also trying to open the sql file but is of 2 gb and put more time to be open and just the first time the program to open it crash. I try again.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 01:12PM

No sorry, I'm wrong.
My actual online site have the db in this way:
Mariadb
Type: Aria
Characters codify: utf8.general_ci

and it has the same on localhost.

But when I open Phpmyadmin on localhost I see in the first page this:
Database Server

Server: Local Databases (127.0.0.1 via TCP/IP)
Server Type: MySQL
Server Version: 5.7.14 - MySQL Community Server (GPL)
Protocol Version: 10
User: root@localhost
Characters Codify Server: UTF-8 Unicode (utf8)

So it's seems that it is using Mysql and not Mariadb, or I'm wrong?



Edited 2 time(s). Last edit at 07/28/2017 01:28PM by markoroots.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 28, 2017 01:25PM

Instead in my online server I have:


Database Server

Server: Localhost via UNIX socket
Server Type: MariaDB
Server Version: 10.1.25-MariaDB-1~trusty - mariadb.org binary distribution
Protocol Version protocol: 10
User: ro756@localhost
Server characters codify: UTF-8 Unicode (utf8)



Edited 1 time(s). Last edit at 07/28/2017 01:28PM by markoroots.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: Otomatic (Moderator)
Date: July 28, 2017 03:07PM

Hi,

Il Wampserver use MySQL and MariaDB, when you launch PhpMyAdmin the default choice is MySQL, you have to choice MariaDB if you want to use it.
It is possible to desactivate MySQL to use only MariaDB.
See the file wamp/mariadb_support.txt

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: July 29, 2017 12:45PM

Perfect. Changing that file I was able to switch to Mariadb in Phpmyadmin.
The problem that also now it give me the same errors but at the end of the import. It look that all the import was made but at the end of "Mariadb Console" I still found those errors.
I have note also that when transfering the files in the prompt sometimes came out an error saying:
ERROR: got a packet bigger them maximum allowed pack

and then the process was continued. I don't know if this is an error that must be corrected or Wampserver recalculate the file and try again to resend it and resend it well.



Edited 1 time(s). Last edit at 07/29/2017 12:46PM by markoroots.

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: August 01, 2017 09:41AM

Any news?

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: August 03, 2017 03:21PM

Any help for me?

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: Otomatic (Moderator)
Date: August 03, 2017 04:07PM

Hi,

What additional "help" would you like to be given?
These are errors in your SQL file, errors that it is up to you to correct.

Also, wanting to import a file larger than 2 Gio with a 32 bit version seems somewhat limited.
You would be interested in exporting / importing table by table or with files well below 2 Gio.

As for the error: ERROR: got a packet bigger them maximum allowed pack
- Wampmanager tray icon -> MariaDB -> MariaDB settings -> max_allowed-packet ->
Then choose a value greater than that exists...

------------------------------------------------------------------------------------------------------------
(W7 Pro 64 bit)-Wampserver 3.1.0 32 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
Wampserver 3.1.0 64 bit - Apache 2.4.27 - PHP 7.1.10/7.0.24/5.6.31 - MySQL 5.7.19 - MariaDB 10.2.8
PhPMyAdmin 4.7.4 - MysqlDumper 1.24.5

Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Problems importing a Mariadb Database
Posted by: markoroots (---.ip156.fastwebnet.it)
Date: August 03, 2017 04:43PM

Hi Otomatic, if is there the problem I will try to fix my db.
I have also setted the max_allowed-packet as you say. I will test it soon.

Really many thanks for your help.

Options: ReplyQuote


Sorry, only registered users may post in this forum.