Rhyous

How to install Bugzilla 3.4.2 on FreeBSD 7.2.

The basic overivew.

1. Install FreeBSD.
   How do I install FreeBSD?

2. Update FreeBSD and download the ports tree.
   What are the first commands I run after installing FreeBSD

3. Then install Apache + SSL.
   Installing an Apache + SSL on FreeBSD using the ports tree

4. Then install MySQL.
   How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

5. Configure MySQL to be Unicode.
   How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

6. Then install Bugzilla

I have previous documents about installing each of the steps above installing Bugzilla. This document will over cover bugzilla.

Installing Bugzilla From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$

su

Password:

ServerName# ServerName# ServerName#

portsnap fetch portsnap extract portsnap udpate

Then just do this to install Bugzilla 3.4.2 on FreeBSD 7.2.

ServerName# ServerName#

cd /usr/ports/devel/bugzilla make BUGZILLADIR=/usr/local/www/apache22/data/bugzilla install

Note: Make sure you choose the correct install directory for the BUGZILLADIR parameter. By default Apache 2.2 is only serving up files in /usr/local/www/apache22/data/ so by install bugzilla there, you will be able to access bugzilla with this url: http://www.YourWebSite.com/bugzilla

You will be asked to select your compile options throughout. If you don’t want to be promtped, and you want to accept the defaults, use this command.

ServerName#

make BATCH=yes install

Now that you have Bugzilla 3.4.2 on your FreeBSD 7.2 server, you are not finished. We now need to connect to connect it to a database, which I am assuming is MySQL but could just as easily be Postgresql.

Resetting the file ownership recursively on the bugzilla folder

Make sure that the bugzilla folder and all subfolders are owned by www:www.

ServerName#

chown -R www:www /usr/local/www/apache22/data/bugzilla

Creating a MySQL Database

1. Log into mysql. I use the command line and type in mysql -p, enter my password when prompted.
2. Create a database for Bugzilla.
3. Create a user that can access Bugzilla.

I use the followiing SQL commands for these steps:

CREATE DATABASE BugDB

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
           CREATE TEMPORARY TABLES, DROP, REFERENCES
           ON BugDB.* TO BugDBUser@localhost
           IDENTIFIED BY 'P@sswd!';
FLUSH PRIVILEGES;

Run Install Check Script

1. In a command prompt go to /usr/local/www/data/bugzilla

   ServerName#

   cd /usr/local/www/data/bugzilla

2. Run the setup checking script.

   ServerName#

   ./checksetup.pl

3. Now you are ready to open and edit the localconfig file.

   ServerName#

   ee localconfig

Change the following values:

$webservergroup = ‘www’
$db_name = ‘BugDB’
$db_user = ‘BugDBUser’
$db_pass = ‘P@sswd!’

Then close and save the localconfig file.

4. Run ./checksetup.pl again.

Note: If you have installled everything including MySQL using the defaults, you will see this warning:

WARNING: You need to set the max_allowed_packet parameter in your MySQL configuration to at least 3276750. Currently it is set to 1048576. You can set this parameter in the [mysqld] section of your MySQL configuration file.

Resolve this using the MySQL configuration file called my.cnf. I discussed the my.cnf earlier in this article, so you should already be familiar with it.
How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

Find the max_allowed_packet settings and change it to 4M.

max_allowed_packet = 4M

Restart MySQL.

ServerName#

/usr/local/etc/rc.d/mysql-server restart

5. Run checksetup.pl again.

   I got this error:

   Creating ./lib/.htaccess…
   No such file or directory at Bugzilla/Install/Filesystem.pm line 445, line 275.

   I had to manually create the /usr/local/www/apache22/data/bugzilla/lib directory then this error disappeared when I ran checksetup.pl again.

6. Now create an Apache configuration file for bugzilla and put it in /usr/local/etc/apache22/Includes. I name it bugzilla.conf.

   bugzilla.conf

   <Directory "/usr/local/www/apache22/data/bugzilla">
     Options +ExecCGI
     AllowOverride Limit
     DirectoryIndex index.cgi
     AddHandler cgi-script .cgi
   </Directory>
   

   Restart Apache

   ServerName#

   /usr/local/etc/rc.d/apache22 restart

You should now be able to connect to your server: http://YourServer/bugzilla

---

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

FreeBSD

Starting MySQL


/usr/local/etc/rc.d/mysql-server start


Stopping MySQL


/usr/local/etc/rc.d/mysql-server stop


Restarting MySQL


/usr/local/etc/rc.d/mysql-server restart


Red Hat

Starting MySQL


/etc/init.d/mysql start


or


/sbin/service mysql start


Stopping MySQL


/etc/init.d/mysql stop


or


/sbin/service mysql stop


Restarting MySQL


/etc/init.d/mysql restart


or


/sbin/service mysql restart


FreeBSD
There are two easy ways on FreeBSD:

From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$

su

Password:

ServerName# ServerName# ServerName#

portsnap fetch portsnap extract portsnap udpate

Then just do this to install MySQL on FreeBSD.

ServerName# ServerName#

cd /usr/ports/databases/mysql51-server make install

Or if you want to use utf8 by default, run this command:

ServerName#

make WITH_CHARSET=utf8 install

MySQL 5.1 Server (and MySQL 5.1 client) will download, compile, and install automagically for you.

From Packages

You can also install easily as a binary package with this simple command.

ServerName#

pkg_add -r mysql51-server

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html

Red Hat
Using RPM

You have to go to the MySQL site and download the MySQL 5.1 server RPM and install it.
http://dev.mysql.com/downloads/

It does not automatically install the MySQL client, you have to download that as a separate RPM and install it.

Using yum

Since I didn’t have a MySQL license, yum didn’t work, so I don’t know if it can be installed using yum.

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html

---

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to create a UTF-8 Unicode Database on MySQL?

I am not going to cover installing, I have done that here:
How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

So when you open MySQL using the command line MySQL client, you can see what Character Set your server is configured to use with this command:

show variables like 'character_set_server';

Often the default is Latin-1. I wish UTF-8 was the default but it is not.

You can see the language your database is created with by using this command:

show create database dbname

Again, usually the default is Latin-1 and again, I wish the default were UTF-8 but it is not.

So how do I make my MySQL database UTF-8?
How do I make UTF-8 the default?

I am going to find out…

Ok, so I have MySQL installed on two different platforms:
FreeBSD 7.2 x64.
Red Hat 5.4 x64.

My question are these:
What level do you set the Unicode setting at? Install instance, database, or column type.

MySQL – Looks like it can be configured globally in the my.cnf or it can be database specific.

To configure globally

Add the following to the my.cnf file:

[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

Note: There are other options for collation besides utf8_general_ci such as utf8_unicode_ci. See this article:
http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

Do I have to create the database in a special way?

Not if you configure the setting globally. However, if you don’t configure unicode support globally then yes you have to create your database in a specific way.

I found this post that is for an applications that uses a MySQL Unicode database. I don’t care about the application, just the MySQL data.
http://dev.mysql.com/doc/refman/5.1/en/create-database.html
http://dev.mysql.com/doc/refman/5.1/en/charset-applications.html

So the syntax will be:

CREATE DATABASE mydb   DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Do I have to compile differently to get unicode support?

I didn’t have to recompile on either FreeBSD or Red Hat.

Is there differences for each platform?

Slight differences.

FreeBSD

FreeBSD has the MySQL client as a dependency so it gets installed with the server with out any extra work.

The Database folder is /var/db/mysql.

For the global configuration there is not a my.cnf file created by default.

FreeBSD has example my.cnf files located here:

/usr/local/share/mysql
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-small.cnf

You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

# # # ? ? ? ? ? ? ? ? #

cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf chown mysql:mysql /var/db/mysql/my.cnf cat << EOF >> /var/db/mysql/my.cnf [mysqld] init_connect=’SET collation_connection = utf8_general_ci’ init_connect=’SET NAMES utf8′ default-character-set=utf8 character-set-server=utf8 collation-server=utf8_general_ci skip-character-set-client-handshake EOF

Red Hat

Red Hat does not have the MySQL client installed with the server, you have to download a separate RPM and install it. But it is really easy. Download both RPMs and install them.

The Database folder is /var/lib/mysql.

For the global configuration there is not a my.cnf file created by default.

Red Hat has example my.cnf files located here:

/usr/share/mysql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Same as FreeBSD, there isn’t one used by default and you have to copy one and use it.
You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

# # # ? ? ? ? ? ? ? ? #

cp /usr/share/mysql/my-medium.cnf /var/lib/mysql/my.cnf chown mysql:mysql /var/lib/mysql/my.cnf cat << EOF >> /var/lib/mysql/my.cnf [mysqld] init_connect=’SET collation_connection = utf8_general_ci’ init_connect=’SET NAMES utf8′ default-character-set=utf8 character-set-server=utf8 collation-server=utf8_general_ci skip-character-set-client-handshake EOF

---

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

Imagine you have a query such as the following:

SELECT * FROM User WHERE LastName='O'Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo'conner','joconner@somedomain.tld')

Well, that is obviously not going to work, because the apostrophe or single quote in the name O’Conner is going to break the query syntax.

You have to have two single quotes to use a quote.

SELECT * FROM User WHERE LastName='O''Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo''conner','joconner@somedomain.tld')

Ok, so there are two ways to make sure you have two quotes in C#:

1. You manage the query string yourself.
2. You use a DataTable and let it manage the query string for you.

Managing the query string yourself

Ok, the answer is simple. You need two single quotes next to each other.

Now, when you have single string, this is easy to do. You need to replace each instance of a single quote with two single quotes using this function which already exists for you:

string.replace(string inStringToBeReplaced, string inNewString)

Here is an example of doing it wrong, then fixing it. Step through this in a debugger.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email     = "joconner@domain.tld";

            // Both these queries are broken because of the space.
            string strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            string strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" +
                        "'" + FirstName + "'," +
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";

            // This will actually break your query too, because it will replace valid single quotes
            // with two single quotes.  You need to do this on the actually data strings.
            strQuery1 = strQuery1.Replace("'", "''"); //
            strQuery2 = strQuery1.Replace("'", "''");

            // Replace any intance of a single quote with two single quotes, ''.
            // IMPORTANT: Typing two single quotes ('') is not the same as a double quote (").
            FirstName = FirstName.Replace("'", "''");
            LastName = LastName.Replace("'", "''");
            UserName = UserName.Replace("'", "''");
            Email = Email.Replace("'", "''");

            // Both these queries are working now;
            strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" +
                        "'" + FirstName + "'," +
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";
        }
    }
}

Using a DataTable to manage this for you automagically

This actually looks like more work at first, but really when handling a lot of data, it is much more easy to code using DataTables and DataRows.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName  = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email        = "joconner@domain.tld";

            // Create the connection
            string mConnectionString = "Data Source=ServerName; user id=UserName; password=pw; Initial Catalog=DatabaseName;";
            SqlConnection mSqlConnection = new SqlConnection(mConnectionString);

            // Create a data adapter, this is what does the magic.
            String mQueryForSqlDataAdapter = "Select * from TableName";
            SqlDataAdapter tmpSqlDataAdapter;
            SqlCommandBuilder tmpSqlCommandBuilder;
            DataTable tmpDataTable = new DataTable();
            tmpSqlDataAdapter = new SqlDataAdapter(mQueryForSqlDataAdapter, mSqlConnection);

            // Use the SqlDataAdapter to create a table with the right schema but no data
            tmpDataTable = tmpSqlDataAdapter.FillSchema(tmpDataTable, SchemaType.Mapped);

            // Create a SqlCommandBuilder
            tmpSqlCommandBuilder = new SqlCommandBuilder(tmpSqlDataAdapter);

            // Create a DataRow and populate it
            DataRow row = tmpDataTable.NewRow();
            row["FirstName"] = FirstName;
            row["LastName"] = LastName;
            row["UserName"] = UserName;
            row["Email"] = Email;

            // Add this row to the DataTable
            tmpDataTable.Rows.Add(row);

            // Write this to the database
            tmpSqlDataAdapter.Update(tmpDataTable);
        }
    }
}

Notice we didn’t have to do a string replace of ‘ for ”.

Ok, so I am in the middle of developing a database tool and so I populate a bunch of data, (by adding a bunch of rows), to a column that is AUTOINCREMENT.

So I have 3725 rows for a feature that is working. Now I am developing other features and debuggin them and so I want to reset the database to the same point it was before I started debugging.

So I am deleting all the rows above 3725, however my next AUTOINCREMENT number continues to go up.

I have this handy little SQL statement that should fix that:

To set the table back to 0.

DBCC CHECKIDENT (MyTable, RESEED, 0)

Now, if I set the table back to 0 that can be a problem if you still have rows in it.

The next row you try to insert will give you an error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Person’. Cannot insert duplicate key in object ‘dbo.Person’.
The statement has been terminated.

So if you have rows, try this:

DECLARE @size int
SET @size=(SELECT COUNT(*) From MyTable)
DBCC CHECKIDENT (MyTable, RESEED, @size)

Happy day, it works!!

There is a Database Publishing Wizard 1.1 you can download that may work for SQL Server 2005, but didn’t work for me with SQL Server 2008. However,Database Publishing Wizard 1.3 is installed with Visual Studio 2008 but I cannot find a separate download. This tool gets you the schema and data and everything but the “drop and create database” script.

So I think you need Visual Studio 2008 for this for SQL Server 2008 to get it. I am not sure why I cannot find it separately. Maybe Microsoft has a reason.

Step 1 – In Visual Studio 2008, go to Tools | Connect to Database and connect to a MS SQL database.

Under the Server Explorer window, the connection now appears.

Step 2 – Expand Data Connections.

Step 3 – Right-click on the connection and choose Publish to provider.

Step 4 – Click Next.

Step 5 – Choose the database.

Step 6 – Click Next.

Step 7 – Select the publishing options (such as to export the schema and data or just the schema).

Step 8 – Choose a file.

Step 9 – Click Finish.

Step 10 – The one thing this is missing is the script to drop and create the database. You can easily get this from Microsoft SQL Server Management Studio 2008 (there is a free Express version if you don’t have it). Just connect to the database, right-click on the database and choose Script Database as | Drop And Create to | Clipboard. Now past this text to the top of your file you just created.

Here is a MySQL Query

SELECT * FROM Table LIMIT 10

Here is a Microsoft SQL Query to perform the same

SELECT TOP10 * FROM Table

Simple question, simple answer

SQL Query to return the data

SELECT TABLE_NAME FROM DBName.INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='Article'

How do I check this in C#?

As for how you check that in C#? You just make sure the Row count returned from the query is 1. See this article I have already posted.

How do I get the number of rows returned from a Microsoft SQL Query in C#?

The following example accomplishes this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace InsertToDatabase
{
	public class InsertToDatabase
	{
		string connectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
		string query = "INSERT INTO Users (Firstname, Lastname, Email) VALUES ('Jared','Barneck','Jared.Barneck@somedomain.tld')";
		SqlConnection connection = new SqlConnection(connectionString);
		SqlCommand command = new SqlCommand(query, connection);
		connection.Open();
		command.ExecuteNonQuery();
		connection.Close();
	}
}