MySQL specific questions

After applying 'mysql_viart_shop.sql' version number has disappeared

This default dump contains test data for installation. If you had to apply it after installation and as a result your version number is no longer visible in admin, run this SQL query:

update va_global_settings set setting_value='4.0.8' where setting_name='number' read more...

Disable all countries

With the help of this MySQL query you can disable all countries at once:

update va_countries set show_for_user = 0

Afterwords you'll just need to check 'Show for User' field for the countries you want.

read more...

Is it possible to choose the prefix I want for database tables

It is possible to use other prefix. You should edit all prefixes in db/mysql_viart_shop.sql file and also in the 'install.php' file.
read more...

Database error: Invalid SQL: INSERT INTO va_tracking_visits

This problem can happen when Site Tracking settings are activated on your site and the tracking data has been gathering for years. Sometimes the tracking data can reach a couple GB and then it starts to burden the database.

We recommend to run the following MySQL queries to clean this data (Tools > DB Management > Run SQL Query):

DELETE FROM va_tracking_visits

and this one:

DELETE FROM va_tracking_pages read more...

How to import predefined serial numbers in database

Here is an example of a SQL query that you can run via admin section (Tools > DB Management >Run SQL Query):

INSERT INTO va_items_serials (item_id, serial_number) VALUES (1, 'Serial #1'), (1, 'Serial #2'), (1, 'Serial #3');

In this example three serial numbers will be added to product with ID 1.

read more...

Connect failed: User ".." already has more than 'max_user_connections' active connections

It seems, there is in place a restriction for maximum number of connections to database and they are all already used. So, no more connections can be opened until previous connections are closed. Such situation may occur if persistent connections are used or some script opens too many connections at once.

You can switch off the persistent connections by editing the 'includes/var_definition.php' file and change the line of the code to be like this one:

$db_persistent = false;

If the problem persists, please send us the valid Admin/FTP to your site so we can check and give you some piece of advice. read more...

Delete all categories

You can run the following SQL queries for that:

TRUNCATE TABLE va_categories

TRUNCATE TABLE va_categories_sites

TRUNCATE TABLE va_categories_post_types

TRUNCATE TABLE va_categories_user_types

read more...

Error message: Connect failed: DB_PORT_ERROR

The above error could occur if MySQL server is down and does not respond to the defined port for some reason, therefore ViArt scripts just could not connect to it. It could also happen if someone has changed database port settings in includes/var_definition.php to incorrect value.

We recommend to contact the hosting provider first and discuss this issue with them.
read more...

MySQL User has exceeded the 'max_questions' resource

MySQL can monitor resource usage - this helps your ISP to keep a record of how many queries your site is consuming. On shared hosts, this can be used to limit the maximum number of queries per domain, leading to less burden on the server.

You can either contact your hosting provider and ask them to increase the max_questions limit or try to keep the usage of your site so low that the limit is not crossed.
read more...

It tells me "ERROR 1049: Unknown database '...

Please check your "includes/var_definition.php" file.
Variable $db_name should contain a valid database name, e.g.
$db_name = "mydb_1";
Run your MySQL client and perform "SHOW DATABASES" command and make sure that the database you chosen for ViArt Shop does exist. read more...

If the database does not exist then create it with "CREATE DATABASE" command

A MySQL command to create a new database is:

create database new_database_name

Read more about CREATE DATABASE syntax at mysql.com read more...

How to add a user for my database?

You could add a user using GRANT command. Note that user should have rights to select, insert, update, delete. A MySQL Grant statement for this user would look like this:

 
GRANT 
  select, insert, update, create, alter, delete, drop 
ON 
 database.* 
TO 
 user@localhost 
IDENTIFIED BY 
 'password';

read more...

How to manually populate my database with SQL script??

ViArt Shop distribution contains "db" folder with SQL scripts for different database structures:

mysql_viart_shop.sql - for MySQL
postgre_viart_shop.sql - for PostgreSQL
access_viart_shop.sql - for Microsoft Access database

To populate your database with sql script run this command in your MySQL client:


mysql -u User -p Database < mysql_viart_shop.sql

Postgre command line is:

psql -U username -W -d dbname < postgre_viart_shop.sql read more...

How to make a backup of my database via command line?

You can make a backup of MySQL database using mysqldump program distributed with MySQL. E.g.:

mysqldump --opt -u User -p Database > VA_Shop_Dump.sql

It should create a file VA_Shop_Dump.sql that will contain the dump of your database. Please read more about dumping MySQL database at mysql.com. read more...

How to access database?

You can install a MySQL client program (e.g. Phpmyadmin) on your server preferably to the folder where your ViArt Shop is located. Then type www.[yoursite.com]/phpmyadmin in the browser address bar, and you will access your database control panel.

Also note, you can run SQL commands directly from ViArt Shop admin using Tools > DB Management > Run SQL Query form. read more...

SQL query to delete all manufacturers in the shop

To delete all manufacturers we can advise to run the following query:

delete from va_manufacturers;

You may also need another query to update products. It is as follows:

update va_items set manufacturer_id=NULL; read more...

Update order numbers

To update orders sequence in your shop run this query:

alter table va_orders auto_increment=100000

where 100000 is your next order number. read more...

Retrieve a list of most read articles in 2012

To receive a list of most viewed (read) articles from certain date run this SQL query in Tools > DB Management > Run SQL Query:

select * from va_articles where article_date >= '2012-01-01 'order by total_views desc limit 10 read more...

Increase the length for user login field

You can use the below SQL query to increase the length of the login field:

ALTER TABLE va_users MODIFY COLUMN login VARCHAR(255) read more...

Use email as login

You can activate email login in Settings > Site Users > User Types > Customer > Profile Settings > Predefined Fields:

To change login for old users please run a SQL query:

update va_users set login=email

Otherwise old users (registered before this change) could still use their old login details. It works this way because email field may not be required during registration so these users need to have a way to login.

After these changes, would ViArt now use the field "login" to populate the field "email" in the order profile?

Yes, it would.

Would this mean that I can remove "Email" from the "show" and "required"

Yes, you can remove it. read more...

Problem with Turksih characters (utf8) in phpMyadmin

On your on risk you may try adding the lines below to the file 'includes/db_mysql.php' just after the lines with @mysql_connect:

mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET COLLATION_CONNECTION = 'utf8_turkish_ci'");

read more...