Manifesto42 – Arquiva-me

Apache to only listen on localhost

My development machine has apache, mySql..etc. installed and its on a work network. All good, but what if one of my coworkers decided to scan the network, get my ip and try to connect to it? Well, if nothing is done, the browser will show my localhost directory, which is bad.

What i needed: to block all access to my apache server from anywhere but this machine. Its pretty simple. (we could do this with an .htaccess file on the root, or even an empty/redirect/whatever index file, but the apache way is fun.

Depending on your server package of choice (but obviously with apache), find where httpd.conf is, open it and do this (on XAMPP, its on [your_install_dir]\apache\conf\):

- Change (this is the default)

Listen 80

to

Listen 127.0.0.1:80

- Find

<Directory “[your_install_dir]“>

and a little below you will see something like “Order deny,allow”..etc., mine is like this:

Order deny,allow
Deny from all
Allow from 127.0.0.1

Which basically blocks everything and allows only what you need.

Restart your apache service and give it a try. It should only open from your machine and no one on the network should be able to see your server.

Good reads:

http://httpd.apache.org/docs/2.2/mod/mod_authz_host.html – And all else relating to Apache is nice also.

MySQL – Create new user

The use case scenario was an installation of Tiny Tiny RSS that doesnt allow an empty MySQL password. My local mysql doesnt have a password set so i was stuck on that screen.

To fix it, i just had to create a new user on my mysql, give it privileges and a non empty password. To do it, i did this:

Connect to your mysql server as root (just open a command prompt and, unless you have you bin mysql dir on your PATH, steer to your bin dir):

c:\whatever> mysql –user=root mysql

(Please note that there are two hyphens before the word “user” on the command above)

After connecting, add the user:

mysql> CREATE USER ‘username’@'localhost’ IDENTIFIED BY ‘user_pass’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@'localhost’ WITH GRANT OPTION;

Obviously replace “username” by the name os the user you want to create and “user_pass” by..you guessed it, its password.

After these steps, you are good to go enjoy your tiny tiny rss install with a non empty mysql password.

As usual, here are some links where you can learn more about this (from MySQL manual):

To read: 6.3.2. Adding USer Accounts (opens in a new window/tab)

MySQL Problem – “MySQL server has gone away” (but it hasn’t)

Had this problem a couple of times already and i better write what i did to solve it. Quite simple really, but it cost me a few hours of searching.

This problem showed up when i was trying to restore large dumps of data into a Database. A few minutes into the restore, it crashed with this error, but the server never went away at any time.

Anyway, to fix this go to your my.ini file, and edit/add this line:

max_allowed_packet = 64M

On my MySQL install, i already had that line, but with “1M” as the value.

Cant recall the last time i got the error, but today, while restoring a dump with more than 80mb, i got the error and noticed the inserts where huge lines of multi data (logically to accelerate insertion), instead of tons of different inserts.

Hope this helps someone, and me if i forget how its fixed next time it happens – which i probably will.

UPDATE && || NOTE: Also keep in mind a few other parameters that you can tweak (do so very carefully, and first do it on a development machine):

key_buffer = 384M
thread_cache_size = 8
query_cache_size = 32M
max_allowed_packet =128M
table_cache = 4096
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
log_error=”mysql_error.log”
wait_timeout=10000

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 180

MySQL Command Line – Part 2

A small update/add to my previous post about MySQL command line, well, commands:

So you know from my previous post how to open mysql command prompt, connect, etc. so now i am just going to leave here how you can restore a dump from inside the mysql prompt:

Assuming the dump is “dump.sql”, you just do:

mysql> source dump.sql

Simple, eh?

How to use your laptop as a wifi hotspot

Imagine the scenario:

- My internet connection is on the top floor and although i have a wifi router that broadcasts a wifi hotspot so i can access all over the house, the first floor doesnt have that much reception.
- Sometimes i have my laptop downstairs, in the living room, where i have wifi.
- Would be nice to, occasionally, have wifi in the bathroom or kitchen.

Solution:

Use your laptop as a wifi hotspot and connect your gadgets, tablets or mobile phones, to it from the bathroom/kitchen.

Here’s how you do it – i only tested this on Windows 8 but its supposed to work on windows 7 also:

- Open a command prompt with administration rights
- Type

netsh wlan set hostednetwork mode=allow ssid=MYNET key=MYPASSWORD

- Replace MYNET by the name you want to give your hotspot (no spaces), and MYPASSWORD by the password you want to give it
- Type

netsh wlan start hostednetwork

- Then you have to go to your internet connection properties and activate the internet sharing:
- You can go through Control Panel, Network and Sharing Settings, Change Adapter Settings, right-click on your active internet connection, go to the Sharing Tab, enable “Allow other network users to connect…”, choose your virtual wifi adaptor (that appeared after you gave those commands above) and press OK

From your tablet/mobile phone you will see a new wifi hotspot. Just connect to it and you are off to a nice kitchen online recipe hunting.

All the credits go to Mike Williams with this article (i also tried the more simple approach he gave but it didnt work so well for me – your mileage might vary):

How to turn your Windows 7 laptop into a wireless hotspot

DELETE rows using SELECT results

ok, so i made this nice query to..well, query two tables to check for missing ids on one of them (but it could be mostly anything else). Then i wanted to delete all the ids that dont have a match. This is kind of easily done by deleting the results from a query, but with attention to a small detail: you cant delete from a table that you are subquerying from. This is solved with a hack

So:

My nice query:

SELECT
r.id_email id_email
FROM
rel_emails_grupos r
LEFT JOIN emails e
on e.id_email=r.id_email
WHERE e.id_email IS NULL

(this query checks table “rel_emails_grupos” for id’s that dont exist in table “emails” and lists them)

Now i want to delete all those rows in “rel_emails_grupos” that resulted from the previous query:

DELETE FROM rel_emails_grupos WHERE id_email IN
(SELECT *FROM
(SELECT
r.id_email id_email
FROM
rel_emails_grupos r
LEFT JOIN emails e
on e.id_email=r.id_email
where e.id_email IS NULL) as t)

Notice that we need to add a subquery just to alias it (“AS t”) so it doesnt generate the error:

[Err] 1093 – You can’t specify target table ‘rel_emails_grupos’ for update in FROM clause

Stay safe out there.

More to read here -> Stack Overflow

Remove line breaks and spaces from Table field

Had an issue with a huge table of email address not really compliant. Some had trailing/ending spaces, others even line breaks. To fix the line breaks, this worked:

update emails_table set email = replace(email, ‘\n’, ”)

To remove trailing/ending spaces, i used this:

update emails_table set email=trim(email)

Go and be happy.

Iterating through a Dictionary

Keeping things simple for anybody who does a search about this subject:

If we have a dictionary like this:

var dictio:Dictionary = new Dictionary();

dictio["france"]=”paris”;

dictio["england"]=”london”;

you can iterate like this:

Iterating through the keys:

for(var country:String in dictio){

trace(country); // this lists england, france

}

Iterating through the values:

for each(var capital:String in dictio){

trace(capital); // this lists london, paris

}

You can easily iterate through both keys and values, but its a hack since theres no native way to do it:

for(var country:String in dictio){

trace(country+” has the capital: “+dictio[country]);

}

The above code produces:

france has the capital: paris
england has the capital: london

Easy but also easily forgetable and confusing.

Resources:

Adobe livedocs about Dictionary

Group and concatenate rows

If we have a table like this:

And we want to list all the museums and on which year they..well, they did something.

Something like this result:

We would go about it like this:

SELECT name,GROUP_CONCAT(year) AS year FROM museums GROUP BY name ORDER BY name ASC,year DESC

The trick is the group_concat. What this does is self explanatory, but still, it groups the same museums and concatenates the years (different rows on the query without grouping) it has done something. Yeah, great explaining of the obvious.

You can elaborate from here with power joins, oranges and pizza.

Recover/reset PostgreSQL password

Ran into some PostgreSQL problems again (what else is new?), this time regarding the password for the windows user postgres creates.

I was replacing postgreSQL x64 for the x86 version to fix some weborb/php/lots of other stuff problems. I uninstalled my x64 version, it left the data folder and user, and when it was reinstalling, it asked for my password. I thought i knew it (i still think i do, but lets assume it was me who forgot it and not postgres), but the setup wouyldnt accept it no matter what.

Anyway, to make things short, to reset the password, you can do this:

- Open a Command Prompt with Admin permissions

- type net user postgres postgres (the abstract syntax is “net user [USER HERE] [PASSWORD HERE]“)

- the password is reset to “postgres”

- Smile.