Can’t Connect to MySQL Server Remotely on Ubuntu

by: devanswers.co

MySQL by default will not allow incoming remote connections for security reasons. In this article we will edit the MySQL config file mysqld.cnf to allow remote connections.

Testing a Remote MySQL Connection

To test a remote MySQL connection in Linux terminal, simply replace username and hostname_or_ip with your own.

mysql -u username -h hostname_or_ip -p

Or via Command Line in Windows:

telnet 192.16.8.1.22 3306

If you are getting an error when trying to connect to your remote MySQL server

ERROR 2003 (HY000): Can't connect to MySQL server on '192.16.8.1.22' (111)

or in Windows Telnet

Connecting To 192.16.8.1.22...Could not open connection to the host, on port 3306: Connect failed

1. Edit MySQL config

You may need to comment out bind-address in the MySQL config file mysqld.cnf.

Open mysqld.cnf with nano editor.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Press CTRL + W and search for bind-address

/etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

The above line is telling MySQL to only accept local connections. Comment out this line by adding # before it so it looks like:

/etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

Restart the MySQL service for changes to take effect.

sudo service mysql restart

Now try to connect to MySQL remotely again.

2. Check Firewall

If you still can’t connect, check if there is a firewall configured on your server. The most common firewall for Ubuntu server is ufw.

Check if ufw is enabled:

sudo ufw status

If it’s enabled, you should see some rules:

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Apache Full                ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Apache Full (v6)           ALLOW       Anywhere (v6)

If MySQL is not listed, add a rule for it.

sudo ufw allow mysql

There should be a rule for MySQL:

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Apache Full                ALLOW       Anywhere
3306                       ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Apache Full (v6)           ALLOW       Anywhere (v6)
3306 (v6)                  ALLOW       Anywhere (v6)

Restart the ufw service:

sudo service ufw restart

Copy from https://devanswers.co/cant-connect-mysql-server-remotely/

Need to generate n rows based on a value in a column t sql

By: gbn

I’ll assume

  1. MyRef etc is a column in TableA
  2. You have a numbers table

Something like:

SELECT * INTO #TableA
FROM
 (
    SELECT  1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
    UNION ALL
    SELECT 2, 2, 'AnotherRef'
) T


;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
   A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.QUANTITY

Copy from: http://stackoverflow.com/questions/6439716/need-to-generate-n-rows-based-on-a-value-in-a-column

Installing MongoDB on Mac

By: STEPHEN KOCH

After we had a Processing workshop with Jer Thorpe he got to talking about Mongo, so we decided to have a gander at this “scalable, high-performance, open source, document-oriented database.” Read on for instructions on how to get it operational on your Mac OS X development machine, so you can start playing with Mongo using PHP.

Setting up MongoDB was easy enough on OS X (Snow Leopard), but getting it to play nice with PHP was a little odd. I think this is because I am using MAMP as my dev environment.

The first thing you will want to do before installing is to head on over to mongodb.org and do the 5 minute online shell tutorial. This will whet your appetite for Mongo and will quickly show you how easy it is to work with.

Install MongoDB

  • Download mongo: http://www.mongodb.org/downloads
  • Unzip into a location of choice. (Note: This is where you will run the daemon)
  • Open terminal and use the following command to make the directory where mongo will store data:
    $ mkdir -p /data/db
  • Start the mongodb server in a separate terminal window (if you get an error, you may need to ‘sudo’ this command):
    $ ./mongodb-xxxxxxx/bin/mongod
  • In a separate terminal window start the interactive shell:
    $ ./mongodb-xxxxxxx/bin/mongo
    > db.foo.save( { a : 1 } )
    > db.foo.find()

And with that you should have a running daemon and be able to use the shell just like you did in the online tutorial. If you receive an error about not being able ‘to create / open lock file’, you should run the command to make the db directory with ‘sudo’.

Getting PHP to behave

If you aren’t using MAMP you may be able to run the following command to install, and you should definitely give this a shot first. This worked successfully on my laptop, but I had issues on the machine running MAMP.

 

sudo pecl install mongo

These are the steps I took to get it working with MAMP:

  • Go to pecl.php.net to dowload the latest package (1.1.3)
  • Unzip and open README.md file. Instructions are simple:
  • Install package:
    $ phpize
    $ ./configure
    $ make
    $ sudo make install
  • This will create the ‘mongo.so’ file in the /modules dir. Copy this over to your MAMP extensions directory. You can find the location in your php.ini file. On my machine the path is ‘/Applications/MAMP/bin/php5.3/lib/php/extensions/no-debug-non-zts-20090626/.’ This will also copy the ‘mongo.so’ file to ‘/usr/lib/php/extensions/no-debug-non-zts-20090626/mongo.so.’
  • Add the following to your php.ini file found in your MAMP installation:
    extension=mongo.so
  • Start MAMP
  • Copy the example code found on php.net, place somewhere accesible from your browser and hit to test
  • You should see output in the browser as well as in the running server

Special Notes

If you want to run php from Terminal you will need to look in /etc for the php.ini file. If it doesn’t exist, you can open php.ini.default found there and create a new document. You will need to set the ‘extensions_dir’ variable to either as well as adding the ‘extension=mongo.db’ line to the file. You can issue the ‘php -i’ command from Terminal to see that you have the mongo extension properly loaded.

As I stated, I was able to run the pecl command from my laptop which basically issued the above commands and copied things to their proper locations except for the MAMP extensions dir. A shorter method to install so I recommend giving that a try first but don’t forget to copy the ‘mongo.so’ file over to the MAMP extensions directory.

Next steps and useful tips

  • Be sure to peruse the output when you issue the commands when installing the Mongo PHP package. It’s a lot of text, but if you are lost as to where things are, this output will tell you.
  • Experiment with PHP commands and create some test databases and collections.
  • For administering MongoDB I recommend RockMongo.
  • Try out some of the other drivers to play with Mongo.

Happy coding!

 

Copy from: http://www.bigspaceship.com/mongodb-on-mac/

Prevent Divide-By-Zero Errors in T-SQL

By Sochinda

NULLIF(expression, expression)

Ex: Column1 / Column2, so when Column2 equal 0 this equation will be error.

How to prvent divide by zero error, we have 2 steps as:

1. using NULLIF

NULLIF(Column2, 0) : it will return NULL when value equal 0

 

2. using ISNULL

ISNULL(NULLIF(Column2, 0), 1) it will return 1 when Column2 value is 0

 

So Column1 /  ISNULL(NULLIF(Column2, 0), 1), it will return Column1 Value when Column2 Value = 0

Faceted navigation in PHP and MySQL

By: mightywebdeveloper.com

In this tutorial we are going to build a simple PHP/MySQL faceted navigation from scratch. In case you don’t know what faceted navigation is (or faceted search, layered navigation), it’s the nice filtering mechanism you see on most e-commerce sites in some form or another next to the product listing (see image).

Magento faceted navigation example

With each newly selected filter the resultset is narrowed down accordingly which allows user to quickly find the items (products, classifieds, articles, etc.) that meet their criteria. This search mechanism is most useful when a dataset needs to be filtered based onmultiple properties.

Unfortunately MySQL does not support faceted search out-of-the-box. There are dedicated external search applications such as SOLR or ElasticSearch which index the facets automatically for filtering. However not everyone has the knowledge or resources required to run such an application. Luckily we can achieve similar results while maintaining good performance using MySQL and PHP by setting up an index table and making some clever table joins.

The example data

You can download an SQL export of all the example data here.
Let me first introduce the dataset we’re going to work our magic on for this example:

table: products
+----+-----------------------+----------+-----------------+--------+
| id | name                  | type     | brand           | colour |
+----+-----------------------+----------+-----------------+--------+
|  1 | Black Jacket by Acme  | jacket   | Acme Apparel    | black  |
|  2 | Blue Jacket by Acme   | jacket   | Acme Apparel    | blue   |
|  3 | Blue Jacket by FF     | jacket   | Fictive Fashion | blue   |
|  4 | Orange Trousers by FF | trousers | Fictive Fashion | orange |
+----+-----------------------+----------+-----------------+--------+

The faceted navigation in this example without any of the filters selected will look something like this:

Type:
Jacket (3)
Trousers (1)
Brand:
Acme Apparel (2)
Fictive Fashion (2)
Colour:
Black (1)
Blue (2)
Orange (1)

The user can filter the product overview simply clicking on the filter links which makes it a form of navigation to the user (hence the term faceted navigation).

Note the item counts next to each facet value, these will be updated to reflect the current resultset whenever a filter is added or removed.

 

Faceted navigation index tables

To perform the faceted search in MySQL we need to create two extra tables. One table contains a list of the product facets and the other contains facts about the products.

table: product_facets
+----+--------+
| id | name   |
+----+--------+
|  1 | type   |
|  2 | brand  |
|  3 | colour |
+----+--------+

table: product_facts
+------------+----------+------------+-----------------+
| product_id | facet_id | facet_name | value           |
+------------+----------+------------+-----------------+
|          1 |        1 | type       | jacket          |
|          1 |        2 | brand      | Acme Apparel    |
|          1 |        3 | colour     | black           |
|          2 |        1 | type       | jacket          |
|          2 |        2 | brand      | Acme Apparel    |
|          2 |        3 | colour     | blue            |
|          3 |        1 | type       | jacket          |
|          3 |        2 | brand      | Fictive Fashion |
|          3 |        3 | colour     | blue            |
|          4 |        1 | type       | trousers        |
|          4 |        2 | brand      | Fictive Fashion |
|          4 |        3 | colour     | orange          |
+------------+----------+------------+-----------------+

We used the following PHP function to automatically populate the facts table with data. You’ll need to make sure the product_facts table gets updated for each CRUD operation on the original dataset.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/**
 * Populates the product_facts table with data from the products table
 * @param object $mysql_connection the mysql connection object
 */
function populate_product_facts($mysql_connection) {
  //empty product_facts table
  mysqli_query($mysql_connection"TRUNCATE product_facts");  
  //fetch all product data
  $result = mysqli_query($mysql_connection"SELECT * FROM products");
  //loop through resultset rows
  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $facet_id = 1;
    //loop through table columns
    foreach ($row as $key => $value) {
      //create facts for all product fields except 'id' and 'name'
      if ($key != "id" && $key != "name") {
        $sql "INSERT INTO product_facts VALUES (" $row['id'] . ",$facet_id,'$key','$value');";
        mysqli_query($mysql_connection$sql);
        $facet_id++;
        echo "Added fact: (".$row['id'].", $facet_id, $key, $value) <br/>";
      }
    }
  }
}

Now that we have set up all the required tables we will look at how to query the tables in the next section.

Querying the data

Let’s say a user just opened the product browse page and hasn’t selected any filters yet. To retrieve the product data we can simply execute the query:

1
SELECT FROM products;

This will return a list of all products.

To construct the faceted navigation HTML we need to query the product_facts table.

1
2
3
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
GROUP BY pf.facet_id, pf.value;

Which produces the following result:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 3 |
| type       | trousers        | 1 |
| brand      | Acme Apparel    | 2 |
| brand      | Fictive Fashion | 2 |
| colour     | black           | 1 |
| colour     | blue            | 2 |
| colour     | orange          | 1 |
+------------+-----------------+---+

As you can see this contains all the data required to output the faceted navigation (including counts).

Now let’s say that a user selects the colour blue so that only blue products are shown. This changes the product query to:

1
2
SELECT FROM products
WHERE colour='blue';

Similarly the query to retrieve the facet data becomes:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
WHERE p.colour='blue'
GROUP BY pf.facet_id, pf.value;

Which results in:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 2 |
| brand      | Acme Apparel    | 1 |
| brand      | Fictive Fashion | 1 |
| colour     | blue            | 2 |
+------------+-----------------+---+

We can add an ORDER BY statement to sort the list of filters alphabetically:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
GROUP BY pf.facet_id, pf.value
ORDER BY pf.facet_name, pf.value;

When the user selects multiple filters we simply add multiple conditions in the WHERE clause based on the selected filters.

In the next post I’ll explain how to create the interactive faceted navigation HTML interface based on the work we’ve done so far.

If you have any questions please leave a comment below!

Copy from: http://www.mightywebdeveloper.com/coding/faceted-navigation-in-php-mysql/