An annoying thing that can happen with MSSQL Server, when you move a database to a new server by backing it up on one and then restoring it on the other, is that the server can tell you the username already exists, when you attempt to create it.
The specific error is:
Error 15023: User already exists in current database.
RESOLUTION:
1. Open a new query.
2. Run the query: USE databasename
3. Run the query (this will show the users for the DB): EXEC sp_change_users_login ‘Report’
4. Run the query: EXEC sp_change_users_login ‘Auto_Fix’, ‘username‘, NULL, ‘password‘
*Note: Replace 'username' with database user name and 'password' with its respective password.
Sunday, 31 March 2013
Error 15023: User already exists in current database.
Basic Mysql Commands
Creating a database
Now we are going to create our database.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
The SHOW DATABASES statement shows all available databases on our system. Note that SQL statements are terminated with a semicolon. There are four databases present. The information_schema, mysql and performance_schema are MySQL system databases. The test database is available as a workspace for users to try things out. It is empty; there are no tables.
mysql> CREATE DATABASE My_Database;
This statement creates a new database. Throughout this tutorial, we will use the My_Database database. To create a new database, we need to have certain privileges. Remember that we have connected to the server with the root user, which is a superuser and has all privileges.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| My_Database |
| mysql |
| performance_schema |
| test |
+--------------------+
Showing all databases, the My_Database database is among them.
mysql> use My_Database;
Database changed
In order to work with a database, we must first select it. We select a specific database with a use command.
mysql> SHOW TABLES;
Empty set (0.00 sec)
The SHOW TABLES statement shows all available tables in a database. Since it is a newly created database, no tables are found.
mysql> source cars.sql
Database changed
Query OK, 0 rows affected (0.20 sec)
Query OK, 1 row affected (0.08 sec)
...
In the first chapter, we have provided some sql scripts to create some tables. We use the source command to execute the cars.sql script, which creates a Cars table for us.
mysql> SHOW TABLES;
+----------------+
| Tables_in_My_Database |
+----------------+
| Cars |
+----------------+
Now the SHOW TABLES statement displays one table available.
mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name | Cost |
+----+------------+--------+
| 1 | Audi | 52642 |
| 2 | Mercedes | 57127 |
| 3 | Skoda | 9000 |
| 4 | Volvo | 29000 |
| 5 | Bentley | 350000 |
| 6 | Citroen | 21000 |
| 7 | Hummer | 41400 |
| 8 | Volkswagen | 21600 |
+----+------------+--------+
And this is the data in the table.
Creating a new user
Similarly to Unix root account, it is advised not to use the MySQL superuser root account for our tasks. We should use the root account only when it is necessary. We rather create a new account that we will use for our tasks. This user will have limited privileges. When using the root user we could accidentally do a lot of harm to our data.
mysql> CREATE USER test_user@localhost IDENTIFIED BY '34klq*';
The above statement created a new user called test_user. The accout has password 34klq*. The user is created, but he has no privileges.
mysql> GRANT ALL ON My_Database.* to test_user@localhost;
This statement grants all privileges to test_user for all database objects on the My_Database database.
Now we are going to create our database.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
The SHOW DATABASES statement shows all available databases on our system. Note that SQL statements are terminated with a semicolon. There are four databases present. The information_schema, mysql and performance_schema are MySQL system databases. The test database is available as a workspace for users to try things out. It is empty; there are no tables.
mysql> CREATE DATABASE My_Database;
This statement creates a new database. Throughout this tutorial, we will use the My_Database database. To create a new database, we need to have certain privileges. Remember that we have connected to the server with the root user, which is a superuser and has all privileges.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| My_Database |
| mysql |
| performance_schema |
| test |
+--------------------+
Showing all databases, the My_Database database is among them.
mysql> use My_Database;
Database changed
In order to work with a database, we must first select it. We select a specific database with a use command.
mysql> SHOW TABLES;
Empty set (0.00 sec)
The SHOW TABLES statement shows all available tables in a database. Since it is a newly created database, no tables are found.
mysql> source cars.sql
Database changed
Query OK, 0 rows affected (0.20 sec)
Query OK, 1 row affected (0.08 sec)
...
In the first chapter, we have provided some sql scripts to create some tables. We use the source command to execute the cars.sql script, which creates a Cars table for us.
mysql> SHOW TABLES;
+----------------+
| Tables_in_My_Database |
+----------------+
| Cars |
+----------------+
Now the SHOW TABLES statement displays one table available.
mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name | Cost |
+----+------------+--------+
| 1 | Audi | 52642 |
| 2 | Mercedes | 57127 |
| 3 | Skoda | 9000 |
| 4 | Volvo | 29000 |
| 5 | Bentley | 350000 |
| 6 | Citroen | 21000 |
| 7 | Hummer | 41400 |
| 8 | Volkswagen | 21600 |
+----+------------+--------+
And this is the data in the table.
Creating a new user
Similarly to Unix root account, it is advised not to use the MySQL superuser root account for our tasks. We should use the root account only when it is necessary. We rather create a new account that we will use for our tasks. This user will have limited privileges. When using the root user we could accidentally do a lot of harm to our data.
mysql> CREATE USER test_user@localhost IDENTIFIED BY '34klq*';
The above statement created a new user called test_user. The accout has password 34klq*. The user is created, but he has no privileges.
mysql> GRANT ALL ON My_Database.* to test_user@localhost;
This statement grants all privileges to test_user for all database objects on the My_Database database.
Basic FTP Commands
The following examples illustrate typical uses of the command ftp for remotely copying, renaming, and deleting files.
ftp server.test.com
This command will attempt to connect to the ftp server at server.test.com. If it succeeds, it will ask you to log in using a username and password. Public ftp servers often allow you to log in using the username "anonymous" and your email address as password. Once you are logged in you can get a list of the available ftp commands using the help function:
ftp> help
This lists the commands that you can use to show the directory contents, transfer files, and delete files.
ftp> ls
This command prints the names of the files and subdirectories in the current directory on the remote computer.
ftp> cd customers
This command changes the current directory to the subdirecotry "customers", if it exists.
ftp> cd ..
Changes the current directory to the parent direcotry.
ftp> lcd images
Changes the current directory [em]on the local computer[/em] to "images", if it exists.
ftp> ascii
Changes to "ascii" mode for transferring text files.
ftp> binary
Changes to "binary" mode for transferring all files that are not text files.
ftp> get image1.jpg
Downloads the file image1.jpg from the remote computer to the local computer. Warning: If there already is file with the same name it will be overwritten.
ftp> put image2.jpg
Uploads the file image2.jpg from the local computer to the remote computer. Warning: If there already is file with the same name it will be overwritten.
ftp> !ls
A '!' in front will execute the specified command on the local computer. So '!ls' lists the file names and directory names of the current directory on the local computer.
ftp> mget *.jpg
With mget you can download multiple images. This command downloads all files that end with ".jgp".
ftp> mput *.jpg
Uploads all files that end with ".jgp".
ftp> mdelete *.jpg
Deletes all files that end with ".jgp".
ftp> prompt
Turns iteractive mode on or off so that commands on multiple files are executed without user confirmation.
ftp> quit
Exits the ftp program.
ftp server.test.com
This command will attempt to connect to the ftp server at server.test.com. If it succeeds, it will ask you to log in using a username and password. Public ftp servers often allow you to log in using the username "anonymous" and your email address as password. Once you are logged in you can get a list of the available ftp commands using the help function:
ftp> help
This lists the commands that you can use to show the directory contents, transfer files, and delete files.
ftp> ls
This command prints the names of the files and subdirectories in the current directory on the remote computer.
ftp> cd customers
This command changes the current directory to the subdirecotry "customers", if it exists.
ftp> cd ..
Changes the current directory to the parent direcotry.
ftp> lcd images
Changes the current directory [em]on the local computer[/em] to "images", if it exists.
ftp> ascii
Changes to "ascii" mode for transferring text files.
ftp> binary
Changes to "binary" mode for transferring all files that are not text files.
ftp> get image1.jpg
Downloads the file image1.jpg from the remote computer to the local computer. Warning: If there already is file with the same name it will be overwritten.
ftp> put image2.jpg
Uploads the file image2.jpg from the local computer to the remote computer. Warning: If there already is file with the same name it will be overwritten.
ftp> !ls
A '!' in front will execute the specified command on the local computer. So '!ls' lists the file names and directory names of the current directory on the local computer.
ftp> mget *.jpg
With mget you can download multiple images. This command downloads all files that end with ".jgp".
ftp> mput *.jpg
Uploads all files that end with ".jgp".
ftp> mdelete *.jpg
Deletes all files that end with ".jgp".
ftp> prompt
Turns iteractive mode on or off so that commands on multiple files are executed without user confirmation.
ftp> quit
Exits the ftp program.
Unable to start MySQL "Error 1067: The process terminated unexpectedly."
In Windows, If you get the following error when you start MySQL service,
Could not start the MySQL service on Local Computer
Error 1067: The process terminated unexpectedly.
Then, follow the fix mentioned below.
1. Start >> Run >> type “cmd”.
2. Go to MySQL binary directory.
In my server, it is “C:\Program Files\MySQL Server 5.0\bin”.
3. Execute the following command “mysqld-nt.exe –console”. If it executes fine, then you need to check the file “my.ini”.
4. Edit the file “my.ini” with the following details and save it.
[mysqld]
port = 3306
basedir=”C:\Program Files\MySQL Server 5.0″
datadir=”c:\Program Files\MySQL Server 5.0\Data”
5. Now you can start MySQL service.
Could not start the MySQL service on Local Computer
Error 1067: The process terminated unexpectedly.
Then, follow the fix mentioned below.
1. Start >> Run >> type “cmd”.
2. Go to MySQL binary directory.
In my server, it is “C:\Program Files\MySQL Server 5.0\bin”.
3. Execute the following command “mysqld-nt.exe –console”. If it executes fine, then you need to check the file “my.ini”.
4. Edit the file “my.ini” with the following details and save it.
[mysqld]
port = 3306
basedir=”C:\Program Files\MySQL Server 5.0″
datadir=”c:\Program Files\MySQL Server 5.0\Data”
5. Now you can start MySQL service.
Cannot Add Attachments Using the SmarterMail Web Interface
Problem :
End users are unable to attach files to emails they send through the webmail interface.
Resolution :
First, verify that the permissions on the MailProcessing folder are set correctly. To verify the permissions, do the following:
Log in to SmarterMail as the system administrator.
Click the settings icon.
Expand the Activation folder.
Click SmarterMail Self Diagnostic.
A new window will open and display folder permissions. It will also indicate if they passed or failed.
If any of the permission tests fail, you can fix it by doing the following:
Using the Windows Explorer, right-click the default folder C:\Program Files\SmareterTools\SmarterMail\MRS\Mailprocessing directory.
Select Properties and then click the Security tab.
Select Network Service and make sure that Modify is checked and then click Apply and OK.
Repeat the process for the Config directory.
Log in to SmarterMail as the system administrator.
Click the settings icon.
Expand the Activation folder.
Click SmarterMail Self Diagnostic.
A new window will open and display folder permissions. It will also indicate if they passed or failed.
Another possible cause for this problem is that disk quotas have enabled on the Network Services Account and it has reached its limit. If so, disable disk quotas on this account and then once this is done rerun the SmarterMail Self Diagnostic to check if the permissions test passes.
End users are unable to attach files to emails they send through the webmail interface.
Resolution :
First, verify that the permissions on the MailProcessing folder are set correctly. To verify the permissions, do the following:
Log in to SmarterMail as the system administrator.
Click the settings icon.
Expand the Activation folder.
Click SmarterMail Self Diagnostic.
A new window will open and display folder permissions. It will also indicate if they passed or failed.
If any of the permission tests fail, you can fix it by doing the following:
Using the Windows Explorer, right-click the default folder C:\Program Files\SmareterTools\SmarterMail\MRS\Mailprocessing directory.
Select Properties and then click the Security tab.
Select Network Service and make sure that Modify is checked and then click Apply and OK.
Repeat the process for the Config directory.
Log in to SmarterMail as the system administrator.
Click the settings icon.
Expand the Activation folder.
Click SmarterMail Self Diagnostic.
A new window will open and display folder permissions. It will also indicate if they passed or failed.
Another possible cause for this problem is that disk quotas have enabled on the Network Services Account and it has reached its limit. If so, disable disk quotas on this account and then once this is done rerun the SmarterMail Self Diagnostic to check if the permissions test passes.
How to use a MySQL test script to verify if your MySQL server is allowing remote connection?
How to use a MySQL test script to verify if your MySQL server is
allowing remote connection as well as to confirm if you are using the
proper server address, database name, username and password. please be
sure that you have enabled PHP functionality as this script does require
PHP.
1. Connect to the server where are hosting the MySQL server instance.
2. Open up a text editor and use this example however be sure to replace the server, username, password and database variables with your own.
=====================
<?php
$link = mysql_connect(‘<server>’, ‘<username>’, ‘<password>’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_select_db(<database>);
?>
=====================
3. Save the script into a PHP file and call it something that you will remember. Make sure that where you save the file is also web accessible.
4. Open up a web browser can enter the path of where the script is located. If the test is successfully you will see the message "Connected Successfully". If not then you will receive a blank screen meaning that either you can not connect remotely or the username, password, server or database name is incorrect and you will need to recheck the value.
1. Connect to the server where are hosting the MySQL server instance.
2. Open up a text editor and use this example however be sure to replace the server, username, password and database variables with your own.
=====================
<?php
$link = mysql_connect(‘<server>’, ‘<username>’, ‘<password>’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected successfully’;
mysql_select_db(<database>);
?>
=====================
3. Save the script into a PHP file and call it something that you will remember. Make sure that where you save the file is also web accessible.
4. Open up a web browser can enter the path of where the script is located. If the test is successfully you will see the message "Connected Successfully". If not then you will receive a blank screen meaning that either you can not connect remotely or the username, password, server or database name is incorrect and you will need to recheck the value.
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) Error with Plesk 9.5
WIth Server 2008 R2, Plesk 9.5 has a bad habbit of not giving itself permissions to the C:\Windows\Assemblies folder. Due to this, ASP and ASP.NET applications will not function correctly. In order to fix this you must follow the steps below.
-
With Windows Explorer, browse to C:\Program Files (x86)\Parallels\Plesk\etc\DiskSecurity\DiskSecurity.xml
-
Right click on DiskSecurity.xml and edit the file with Notepad or another plain text editor.
-
Insert the following line at the bottom of the file, BEFORE </Entries>:
<Entry AccounType="1" Account="Psacln" Path="C:\Windows\assembly" AceFlags="FilesOnly" AccessMask="Read" EntryFlags="0x0" />
-
Save and close the file.
-
Open a CMD Prompt and navigate to C:\Program Files (x86)\Parallels\Plesk\admin\bin
-
Once you are at the root of the foler type the following command:
ApplySecurity --apply
-
Execute the command, open Task Manager, look at the Processes tab, and find ApplySecurity.exe
-
Wait for the process to terminate, close task manager.
-
Stop Plesk, Reset IIS, Start Plesk
-
Verify that ASP is now working correctly by visiting an ASP Page or ASP based application hosted on the server.
Subscribe to:
Posts (Atom)