Nagesh Blog

Importing MySQL Databases via SSH

2019-02-15T05:44:00.000-08:00

Open the .SQL file in a text editor to ensure it is formatted to use the correct database. There should be a use database command at the top, and it should reference the database that is receiving the imported data. If this command is not included, you can add or use it once you log in to MySQL.
Log in to SSH as the root user. If you do not know how to do this, see How do I access MySQL using SSH?
To log in to MySQL, type mysql -u username -p, where username is your database username.
When you see Enter password:, enter your password for that MySQL user.
If you did not include the use database command in the .SQL file, select the target database of the import. Do this by typing use database_name; and then press Enter.
Type SOURCE /path/to/sql/file.sql, and then press Enter.
Monitor the running import log to check for errors.
To confirm the data was imported correctly, type show tables; and then press Enter.
Type select * from table; and then press Enter. Your imported MySQL database tables display.

Port Forwarding Tester

2018-12-06T23:18:00.002-08:00

The open port checker is a tool you can use to check your external IP address and detect open ports on your connection. This tool is useful for finding out if your port forwarding is setup correctly or if your server applications are being blocked by a firewall. This tool may also be used as a port scanner to scan your network for ports that are commonly forwarded. It is important to note that some ports, such as port 25, are often blocked at the ISP level in an attempt to prevent malicious activity.

Resource: https://www.yougetsignal.com/tools/open-ports/

How-to extract from mysqldump

2017-04-05T06:20:00.000-07:00

The Mysqldumpsplitter can extract database, table, all databases, all tables or tables matching on regular expression. It can also extract multiple tables from one database or describe the contents from a mysqldump.

The tool can extract a MySQL database from a compressed or uncompressed mysqldump. It by default produces the compressed output of sqls in default out directory but you can specify target directory or uncompressed output as well.

Download: https://github.com/kedarvj/mysqldumpsplitter

Source: http://kedar.nitty-witty.com/blog/how-to-recipes-for-mysqldumpsplitter

101 Tips to MySQL Tuning and Optimization

2016-10-14T01:56:00.001-07:00

MySQL is a powerful open-source database. With more and more database driven applications, people have been pushing MySQL to its limits. Here are 101 tips for tuning and optimizing your MySQL install. Some tips are specific to the environment they are installed on, but the concepts are universal. I have divided them up into several categories to help you with getting the most out of MySQL:

MySQL Server Hardware and OS Tuning:

1. Have enough physical memory to load your entire InnoDB file into memory – InnoDB is much faster when the file can be accessed in memory rather than from disk.
2. Avoid Swap at all costs – swapping is reading from disk, its slow.
3. Use Battery-Backed RAM.
4. Use an advanced RAID – preferably RAID10 or higher.
5. Avoid RAID5 – the checksum needed to ensure integrity is costly.
6. Separate your OS and data partitions, not just logically, but physically – costly OS writes and reads will impact your database performance.
7. Put your mysql temp space and replication logs on a separate partition than your data – background writes will impact your database when it goes to write/read from disk.
8. More disks equals more speed.
9. Faster disks are better.
10. Use SAS over SATA.
11. Smaller disks are faster than larger disks, especially in RAID configs.
12. Use Battery-Backed Cache RAID controllers.
13. Avoid software raids.
14. Consider using Solid State IO Cards (not disk drives) for your data partition – these cards can sustain over 2GB/s writes for almost any amount of data.
15. On Linux set your swappiness value to 0 – no reason to cache files on a database server, this is more of a web server or desktop advantage.
16. Mount filesystem with noatime and nodirtime if available – no reason to update database file modification times for access.
17. Use XFS filesystem – a faster, smaller filesystem than ext3 and has more options for journaling, also ext3 has been shown to have double buffering issues with MySQL.
18. Tune your XFS filesystem log and buffer variables – for maximum performance benchmark.
19. On Linux systems, use NOOP or DEADLINE IO scheduler – the CFQ and ANTICIPATORY scheduler have been shown to be slow vs NOOP and DEADLINE scheduler.
20. Use a 64-bit OS – more memory addressable and usable to MySQL.
21. Remove unused packages and daemons from servers – less resource stealing.
22. Put your host that use MySQL and your MySQL host in a hosts file – no dns lookups.
23. Never force kill a MySQL process – you will corrupt your database and be running for the backups.
24. Dedicate your server to MySQL – background processes and other services can steal from the db cpu time.

MySQL Configuration:

25. Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.
26. Avoid O_DIRECT and EXT3 filesystem – you will serialize all your writes.
27. Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory – less reads from disk.
28. Do not make innodb_log_file_size too big, with faster and more disks – flushing more often is good and lowers the recovery time during crashes.
29. Do not mix innodb_thread_concurrency and thread_concurrency variables – these two values are not compatible.
30. Allocate a minimal amount for max_connections – too many connections can use up your RAM and lock up your MySQL server.
31. Keep thread_cache at a relatively high number, about 16 – to prevent slowness when opening connections.
32. Use skip-name-resolve – to remove dns lookups.
33. Use query cache if your queries are repetitive and your data does not change often – however using query cache on data that changes often will give you a performance hit.
34. Increase temp_table_size – to prevent disk writes.
35. Increase max_heap_table_size – to prevent disk writes.
36. Do not set your sort_buffer_size too high – this is per connection and can use up memory fast.
37. Monitor key_read_requests and key_reads to determine your key_buffer size – the key read requests should be higher than your key_reads, otherwise you are not efficiently using your key_buffer.
38. Set innodb_flush_log_at_trx_commit = 0 will improve performance, but leaving it to default (1), you will ensure data integrity, you will also ensure replication is not lagging
39. Have a test environment where you can test your configs and restart often, without affecting production.

MySQL Schema Optimization:

40. Keep your database trim.
41. Archive old data – to remove excessive row returns or searches on queries.
42. Put indexes on your data.
43. Do not overuse indexes, compare with your queries.
44. Compress text and blob data types – to save space and reduce number of disk reads.
45. UTF 8 and UTF16 is slower than latin1.
46. Use Triggers sparingly.
47. Keep redundant data to a minimum – do not duplicate data unnecessarily.
48. Use linking tables rather than extending rows.
49. Pay attention to your data types, use the smallest one possible for your real data.
50. Separate blob/text data from other data if other data is often used for queries when blob/text are not.
51. Check and optimize tables often.
52. Rewrite InnoDB tables often to optimize.
53. Sometimes, it is faster to drop indexes when adding columns and then add indexes back.
54. Use different storage engines for different needs.
55. Use ARCHIVE storage engine for Logging tables or Auditing tables – this is much more efficient for writes.
56. Store session data in memcache rather than MySQL – memcache allows for auto-expiring values and prevents you from having to create costly reads and writes to MySQL for temporal data.
57. Use VARCHAR instead CHAR when storing variable length strings – to save space since CHAR is fixed length and VARCHAR is not (utf8 is not affected by this).
58. Make schema changes incrementally – a small change can have drastic effects.
59. Test all schema changes in a development environment that mirrors production.
60. Do NOT arbitrarily change values in your config file, it can have disastrous affects.
61. Sometimes less is more in MySQL configs.
62. When in doubt use a generic MySQL config file.

Query Optimization:

63. Use the slow query log to find slow queries.
64. Use EXPLAIN to determine queries are functioning appropriately.
65. Test your queries often to see if they are performing optimally – performance will change over time.
66. Avoid count(*) on entire tables, it can lock the entire table.
67. Make queries uniform so subsequent similar queries will use query cache.
68. Use GROUP BY instead of DISTINCT when appropriate.
69. Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.
70. Keep indexes simple, do not reuse a column in multiple indexes.
71. Sometimes MySQL chooses the wrong index, use USE INDEX for this case
72. Check for issues using SQL_MODE=STRICT.
73. Use a LIMIT on UNION instead of OR for less than 5 indexed fields.
74. Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid the SELECT prior to update.
75. Use a indexed field and ORDER BY instead of MAX.
76. Avoid using ORDER BY RAND().
77. LIMIT M,N can actually slow down queries in certain circumstances, use sparingly.
78. Use UNION instead of sub-queries in WHERE clauses.
79. For UPDATES, use SHARE MODE to prevent exclusive locks.
80. On restarts of MySQL, remember to warm your database, to ensure that your data is in memory and queries are fast.
81. Use DROP TABLE then CREATE TABLE instead of DELETE FROM to remove all data from a table.
82. Minimize the data in your query to only the data you need, using * is overkill most of the time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark queries, including using load on the server, sometimes a simple query can have affects on other queries.
85. When load increases on your server, use SHOW PROCESSLIST to view slow/problematic queries.
86. Test all suspect queries in a development environment where you have mirrored production data.

MySQL Backup Procedures:

87. Backup from secondary replicated server.
88. Stop replication during backups to prevent inconsistencies on data dependencies and foreign constraints.
89. Stop MySQL altogether and take a backup of the database files.
90. Backup binary logs at same time as dumpfile if MySQL dump used – to make sure replication does not break.
91. Do not trust an LVM snapshot for backups – this could create data inconsistencies that will give you issues in the future.
92. Make dumps per table for easier single table recovery – if data is isolated from other tables.
93. Use –opt when using mysqldump.
94. Check and Optimize tables before a backup.
95. When importing temporarily disable foreign constraints for a faster import.
96. When importing temporarily disable unique checks for a faster import.
97. Calculate size of database/tables data and indexes after each backup to monitor growth.
98. Monitor slave replication for errors and delay with a cron script.
99. Perform Backups regularly.
100. Test your backups regularly.

And finally 101: Perform MySQL monitoring: Monitis Unveils The World’s First Free On-demand MySQL Monitoring.

Reference:
http://www.monitis.com/blog/101-tips-to-mysql-tuning-and-optimization/

Installing Solr 4.6 on Windows

2015-03-27T05:45:00.003-07:00

The steps below are a guide to help you install Solr versions 4.1 to 4.6. Here, I tested Solr 4.6 installation on Windows 7 and Windows 8.1.

1. Download and install Java SDK (I installed jdk-7u45-windows-x64.exe).

2. Download and install Apache Tomcat (I installed apache-tomcat-8.0.0-RC5.exe). Usually, Tomcat is installed on port 8983 (but this is optional- you can specify your own port).

3. Test the Tomcat server in your browser. You should see the following screen:

4. You can configure the Tomcat server by going to Windows > Start > Monitor Tomcat

5. Stop the Tomcat server by going to Windows > Start > Monitor Tomcat > General Tab> Stop

6. Download Solr-4.6.0 and unzip it in your local directory i.e. C:\ Solr-4.6.0 (download zip file).
7. Go to downloaded Solr folder above (step 6) and Copy solr.war file to Apache webapps folder. I.e. Copy C:\solr-4.6.0\dist\solr-4.6.0.war file to C:\Program Files\Apache Software Foundation\Tomcat 8.0\webapps folder (rename solr-4.6.0.war to solr.war).
8. Create an empty Solr home folder. i.e. C:\solr
9. Go to downloaded Solr folder above (step 6). Copy all files from C:\solr-4.6.0\solr-4.6.0\example\solr folder to C:\solr (Solr home folder). This will be your Solr home folder.
10. Look into C:\solr and you will see two folders with name collection1 and bin.
11. Copy the jars from C:\solr-4.6.0\example\lib\ext (all jars) into C:\Program Files\Apache Software Foundation\Tomcat 8.0\lib (this is your Tomcat server main library directory).
12. Set the Java system property solr.solr.home to your Solr Home. Go to Windows > Start > Monitor Tomcat > Java Tab > Java Options. Enter the following entry at the end (see below screenshot): -Dsolr.solr.home=c:\solr

13. Restart Tomcat by going to Windows > Start > Monitor Tomcat > General Tab > Start

14. Test Solr by going to http://localhost:8983/solr/ in your browser. You should see the Solr admin page below.

Source: http://blog.navigationarts.com/installing-solr-4-6-on-windows/

w2ui (New JavaScript UI Library)

2015-03-11T07:44:00.001-07:00

w2ui (New JavaScript UI Library)

Popups and Overlays and many more

Popups and overlays are popular UI elements of modern web applications. If used right, it can significantly enhance user interaction, provide clearity and improve navigation of your application. The w2ui library has both popups and overlays. A popup blocks the content of the application for the duration of user interaction. An overlay, in turn, is non-blocking UI solutions that displays additional information.

Link: http://w2ui.com/web/demo/popup

Link: http://w2ui.com/web/demos/#!popup/popup-8

More Demos

Database management MemCacheD General Usage

2015-03-07T02:21:00.001-08:00

A very good Memcache tutorial video --

Search Engines: Comparison between Solr and Sphinx

2015-02-26T21:17:00.000-08:00

Similarities:

Both Solr and Sphinx satisfy all of your requirements. They’re fast and designed to index and search large bodies of data efficiently.
Both have a long list of high-traffic sites using them
Both offer commercial support. (Solr, Sphinx)
Both offer client API bindings for several platforms/languages (Sphinx, Solr)
Both can be distributed to increase speed and capacity (Sphinx, Solr)

Solr Advantages:

Solr, being an Apache project, is obviously Apache2-licensed. Sphinx is GPLv2. This means that if you ever need to embed or extend (not just “use”) Sphinx in a commercial application, you’ll have to buy a commercial license (rationale)
Solr is easily embeddable in Java applications.
Solr is built on top of Lucene, which is a proven technology over 8 years old with a huge user base (this is only a small part). Whenever Lucene gets a new feature or speedup, Solr gets it too. Many of the devs committing to Solr are also Lucene committers.
Solr can be integrated with Hadoop to build distributed applications
Solr can be integrated with Nutch to quickly build a fully-fledged web search engine with crawler.
Solr can index proprietary formats like Microsoft Word, PDF, etc. Sphinx can’t.
Solr comes with a spell-checker out of the box.
Solr comes with facet support out of the box. Faceting in Sphinx takes more work.
Sphinx doesn’t allow partial index updates for field data.
In Sphinx, all document ids must be unique unsigned non-zero integer numbers. Solr doesn’t even require an unique key for many operations, and unique keys can be either integers or strings.
Solr supports field collapsing (currently as an additional patch only) to avoid duplicating similar results. Sphinx doesn’t seem to provide any feature like this.
While Sphinx is designed to only retrieve document ids, in Solr you can directly get whole documents with pretty much any kind of data, making it more independent of any external data store and it saves the extra roundtrip.
Solr, except when used embedded, runs in a Java web container such as Tomcat or Jetty, which require additional specific configuration and tuning (or you can use the included Jetty and just launch it with java -jar start.jar). Sphinx has no additional configuration.

Unless you need to extend the search functionality in any proprietary way, Sphinx is your best bet.

Sphinx Advantages:

Development and setup is faster
Much better (and faster) aggregation. This was the killer feature for us.
Not XML. This is what ultimately ruled out Solr for us. We had to return rather large result sets (think hundreds of results) and then aggregate them ourselves since Solr aggregation was lacking. The amount of time to serialize to and from XML just absolutely killed performance. For small results sets though, it was perfectly fine.
Best documentation I’ve seen in an open source app
Sphinx integrates more tightly with RDBMSs, especially MySQL

Source : http://stackoverflow.com/questions/1284083/choosing-a-stand-alone-full-text-search-server-sphinx-or-solr

Memcached Vs Redis, which one to pick for large web app?

2015-02-26T06:42:00.003-08:00

Memcachedis a general-purpose distributed memory caching system. It is often used to speed up dynamic database-driven websites by caching data and objects in RAM to reduce the number of times an external data source (such as a database or API) must be read.

Redis is a flexible, open source and advanced key-value store. It is referred to as a “data structure server” where keys can contain strings, lists, hashes, sets and sorted sets of strings.

The main differences between them are listed below:

Installation:

Comparing ease of Installation Redis is much easier. No dependencies required.

Memory Usage:
For simple key-value pairs memcached is more memory efficient than Redis. If you use Redis hashes, then Redis is more memory efficient.

Persistence:
If you are using Memcached then data is lost with a restart and rebuilding cache is a costly process. On the other hand, Redis can handle persistent data. By default, Redis syncs data to the disk at least every 2 seconds.

Replication:
Memcached does not supports replication. Whereas Redis supports master-slave replication. It allows slave Redis servers to be exact copies of master servers. Data from any Redis server can replicate to any number of slaves.

Storage type:
Memcached stores variables in it’s memory. It retrieve any information directly from the servers memory instead of hitting the database again. On the other hand, Redis is like a database that resides in memory. It executes (read and write) a key/value pair from its database to return the resultset and all data resides in memory.Developers are using Redis also for real-time metrics, analytics.

Read/Write Speed:
Memcached is very good to handle high traffic websites. It can read lots of information at a time and give you back at a great response time. Redis can also handle high traffic on read but also can handle heavy writes as well.

Data Structure:
Memcached uses string and integer as data structure. Everything you save can be either one or the other. With integer, the only data manipulation you can do is adding or subtracting them. If you need to save arrays or objects, you will have to serialize them first and then save them. To read them back, you will need to un-serialize.
In comparison Redis has a stronger data structures. It can handle not only strings, integer but also binary-safe strings, lists of binary-safe strings, sets of binary-safe strings and sorted sets.

Key Length:
Memcached key length has a maximum of 250 bytes, whereas Redis key length has a maximum of 2GB.

If you need advanced data structures or disk-backed persistence, you should look into Redis. On the other hand, you might want to stick to Memcached for its simplicity, reliability and speed.

Git Bash Common Command

2014-09-18T05:09:00.001-07:00

- git checkout branch-name
- git pull origin branch-name
- git push origin branch-name
- git merge --no-ff

- git checkout -b

- git fetch
- get log
- for committing files on git-
- git add
- git add . {for committing all files}
- git commit -m 'comment'

MySQL - Character Sets and Collations

2014-08-29T04:59:00.003-07:00

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.
Suppose that we have an alphabet with four letters: 'A', 'B', 'a', 'b'. We give each letter a number: 'A' = 0, 'B' = 1, 'a' = 2, 'c' = 3. The letter 'A' is a symbol, the number 0 is the encoding for 'A', and the combination of all four letters and their encodings is a character set.
Now, suppose that we want to compare two string values, 'A' and 'B'. The simplest way to do this is to look at the encodings: 0 for 'A' and 1 for 'B'. Because 0 is less than 1, we say 'A' is less than 'B'. Now, what we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): "compare the encodings." We call this simplest of all possible collations a binary collation.
But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters 'a' and 'b' as equivalent to 'A' and 'B'; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.

Using this example, you can change character set and collation for a MySQL database table(s).
Most likely you will be need to do this if you haven’t specified character set and collation at the time of database/table creation and default character set/collation applied are not desirable.

------------------------------------------------------------------------------------------------------------------

Setting MySQL default character set and collation in my.cnf

Below are settings for MySQL version 5.5.9 and onwards.
Put them in /etc/mysql/my.cnf is correct sections. Please be careful as some settings might be already present.

[mysqld]
character-set-server=utf8 
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
init_connect='SET collation_connection = utf8_unicode_ci' 
skip-character-set-client-handshake

Next, restart mysql and log into mysql shell:

mysql> show variables like "%character%";show variables like "%collation%";

Sample output as:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Checking current character set and collation for database/table/columns

For Database:

SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA  
WHERE schema_name = "databasename";

It will show output as:

+----------------------------+------------------------+
| default_character_set_name | default_collation_name |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+

For Tables:

SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "databasename";

Sample output as below:

+-----------------------------------------------------+-------------------+--------------------+
| table_name                                          | table_collation   | character_set_name |
+-----------------------------------------------------+-------------------+--------------------+
| rtc_wp_rtAccountToken                               | latin1_swedish_ci | latin1             |
| rtc_wp_rtAccountVerify                              | latin1_swedish_ci | latin1             |
| rtc_wp_rt_crm_mail_messageids                       | latin1_swedish_ci | latin1             |
| rtc_wp_w3tc_cdn_queue                               | latin1_swedish_ci | latin1             |
| gp_meta                                             | utf8_general_ci   | utf8               |
+-----------------------------------------------------+-------------------+--------------------+

For Columns:

SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.`COLUMNS` C 
WHERE character_set_name != 'NULL' AND table_schema = "db_name"

Sample Output:

+------------------------+--------------+--------------------+-------------------+
| table_name             | column_name  | character_set_name | collation_name    |
+------------------------+--------------+--------------------+-------------------+
| rtc_wp_rtAccountToken  | accesstoken  | latin1             | latin1_swedish_ci |
| rtc_wp_rtAccountToken  | refreshtoken | latin1             | latin1_swedish_ci |
| rtc_wp_rtAccountVerify | email        | latin1             | latin1_swedish_ci |
| rtc_wp_rtAccountVerify | type         | latin1             | latin1_swedish_ci |
| rtc_wp_rtAccountVerify | code         | latin1             | latin1_swedish_ci |
+------------------------+--------------+--------------------+-------------------+

Converting character set and collations

MAKE BACKUP

We are serious. Just use mysqldump rather than regretting it later

Changing Database Character Sets and Collations

This is simplest:

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Replace your database name with db_name. Also after running query verify if database-level defaults are changed indeed.

Changing Tables Character Sets and Collations

Below is a syntax to covert character set ofwp_posts and wp_postmetatables.

alter table wp_posts convert to character set utf8 collate utf8_unicode_ci;
alter table wp_postmeta convert to character set utf8 collate utf8_unicode_ci;

If you want to covert all your MySQL tables, then run a command like below on database db_wordpress

mysql -e "SELECT concat('alter table ', TABLE_NAME , ' convert to character set utf8 collate utf8_unicode_ci;')
FROM information_schema.TABLES
WHERE table_schema = 'db_wordpress'
AND TABLE_COLLATION = 'latin1_swedish_ci'" |
tail -n+2 > collation.sql

After you run above query, check collation.sql content to verify if all rows are correct. If collation.sql is empty, you probably do not have a table using MyISAM engine.
If all looks good, run following to convert all mysql tables to InnoDB.

mysql db_wordpress < collation.sql

Changing Column Character Sets and Collations

Below is syntax to convert columns to utf8

alter table table_name change col_name col_name col_data_type character set utf8;

Please note that we have to use same col_name twice!
col_data_type can be found form a sql query like…

mysql> SELECT table_name, column_name, data_type, character_set_name, collation_name FROM information_schema.`COLUMNS` WHERE  table_schema = "db_name" AND table_name = "table_name" AND column_name = "col_name";

Sample output:

+--------------+--------------+-----------+
| table_name   | column_name  | data_type |
+--------------+--------------+-----------+
| wp_posts     | post_content | longtext  |
+--------------+--------------+-----------+

Example for wordpress’s wp_posts table

alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;

Please be very careful for column conversion. Specially if you have non-english characters stored in database.

Source: https://rtcamp.com/tutorials/mysql/character-sets-collations/

TortoiseGit - git not found

2014-08-21T08:16:00.005-07:00

While this question is still hot... some nice people contributed lots of bugfixes to all three projects, so this is what I did to get TortoiseGit on Win7x64, previously failing on all combinations:

install mSysGit (network installer) into C:\msysgit, it will download the source and compile it leaving you in a bash git prompt. Stable version: msysGit-netinstall-1.7.2.3-preview20100911.exe 13 sep
install Git “preview” into C:\Program Files (x86)\Git, choose OpenSSH for ssh link Stable version: Git-1.7.2.3-preview20100911.exe 13 sep
install tortoisegit into C:\Program Files\TortoiseGit, (x64 version) and configure it’s settings specifying the git path (C:\msysgit\bin) and menu integration. Stable version: TortoiseGit-1.5.6.0-64bit.msi 25 sep

This setup picked up my existing git repos made on WinXP x86 with older versions of the packages, and seems fairly stable and fully functional.

Source: http://stackoverflow.com/questions/1389281/tortoisegit-git-not-found

Creating webservice server and client using NuSOAP

2014-07-31T07:23:00.001-07:00

Brief description of NuSOAP:

NuSOAP is a rewrite of SOAPx4, provided by NuSphere and Dietrich Ayala. It is a set of PHP classes – no PHP extensions required – that allow developers to create and consume web services based on SOAP 1.1, WSDL 1.1 and HTTP 1.0/1.1.

Why using NuSOAP? PHP5 already has soapServer and soapClient implementation

it’s easy to implement and fast, no need to create WSDL document by yourself (this is my main reason why I choose NuSOAP)
compatibility, no special PHP extension need (I didn’t test it if still works on PHP4, but it does work flawlessly on PHP 5.0)

First, download NuSOAP from here: http://sourceforge.net/projects/nusoap/
The Server
We will create a SOAP server with two entry points (=function). One function take one parameter and output a string while the other one take two parameters and output a complex result (an array/struct). The codes with commentary:

Source1 : http://www.ahowto.net/php/creating-webservice-server-and-client-using-nusoap
Source2: http://www.scottnichol.com/nusoapprog.htm

The Ultimate Guide To UTF-8 and MySQL

2014-07-11T23:52:00.005-07:00

The Ultimate Guide To UTF-8 and MySQL

How character encodings work in MySQL seem to continue baffle people, at least based on the number of questions posted on Stackoverflow. Read on if you have an application that still outputs funny text like “seÃ±or” or “se�or” where “señor” is expected.

The truth about text in MySQL

Strings in MySQL consist of an encoding marker and a list of bytes. The CHARSET function reads the encoding while HEX can be used to read bytes:

SELECT CHARSET("€"), HEX("€")
=> latin1, 80

This enables MySQL to use different encodings while working on a query. You can write a query that compares text columns in different encodings and returns the results where the text is the same, rather than the bytes that are stored.

I have everything set to `utf8` and still the database gives me garbage!

That’s because of the MySQL client encoding. One query can return text in several different encodings, it’s unreasonable to expect the user software to check the encoding of each individual string. So all text returned by the MySQL client is encoded in only one encoding.
The fast and easy solution to encoding problems: set the MySQL client encoding.
How this is done depends on what you use to connect. If you work in PHP and the old MySQL functions you should use mysql_set_charset("utf8"). If you have an old version try mysql_query("SET NAMES utf8"). If you use MySQLi it would be mysqli_set_charset($link, "utf8") (or $link->set_charset("utf8") if you use the OO API).

What are the encodings in the tables good for, then?

When you create a table you can specify a character encoding for a column. It means how the MySQL server will convert text like “abc123″ to bytes that can be written to disk. The encoding affects two things:

the range of characters that can be stored, and
the number of bytes the text will occupy on disk.

Popular encodings for storage include latin1 and utf8:

latin1: the characters that you can store cover most European languages, and the text occupies 1 byte per character.
utf8: you can store any(*) Unicode characters, and the text occupies from 1 to 3 bytes per character, depending on the character.

Other encodings exist, of course. For example latin2 has characters used in Eastern European languages. If you use ucs2 you can store any Unicode characters and the text occupies 2 bytes per character. If you are storing a lot of text in Japanese you may want to use ucs2 instead of utf8 because you are saving a third in the storage.
You can also specify a collation like utf8_general_ci for a column. It means how MySQL will sort the data. This affects indices and ORDER BY clauses. Cultures have different rules for alphabetic order: for example in Swedish Ä is the second to last letter of the alphabet, while in English it’s equivalent to A. So with Swedish collation you get a < b < ä and with English collation you get a = ä < b. Use the ordering your users expect. You can read more about collations in my other post.
What if you don’t specify an encoding and collation for the column? Then MySQL will use the default specified for the table, the database, or the server.

Converting encodings

Strings in MySQL consist of an encoding marker and a list of bytes. To convert strings from one encoding to another you can use the CONVERT(… USING …) function. What CONVERT actually does is change both the marker and the bytes so that the resulting string is the same as the original; not very useful for fixing broken text.
The solution is stripping the encoding marker from the string before passing it to CONVERT. This is done by converting the string to “binary” first:

CONVERT(BINARY broken_text USING utf8)

I’ve written a detailed case study on fixing a particularly complicated MySQL encoding problem in another post.

Practical demonstration

Let’s create a table with columns in different encodings and fill it with some data.

CREATE TABLE test_encoding (
  utf8   varchar(4) CHARACTER SET utf8,
  latin1 varchar(4) CHARACTER SET latin1,
  latin2 varchar(4) CHARACTER SET latin2
);
INSERT INTO test_encoding (utf8,latin1,latin2) values ('A','A','A');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ñ','Ñ','Ñ');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ŕ','Ŕ','Ŕ');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('☺','☺','☺');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('€','€','€');

When you select the data from this table with select * from test_encoding you get the following results:

+------+--------+--------+
| utf8 | latin1 | latin2 |
+------+--------+--------+
| A    | A      | A      |
| Ñ    | Ñ      | ?      |
| Ŕ    | ?      | Ŕ      |
| ☺    | ?      | ?      |
| €    | €      | ?      |
+------+--------+--------+

Even though each column is encoded differently everything is still readable. This is thanks to the MySQL client encoding. The characters that could not be encoded are replaced with question marks: there is no Ñ in latin2, nor is there a Ŕ in latin1. The smiley face is not present in either.
The last line is interesting: the euro sign can be encoded in latin1 although technically it’s not really a latin1 character. This shows that MySQL’s latin1 is not really the standard ISO-8859-1 encoding, commonly known as “ISO latin 1″. In reality it is Windows-1252, the “Western European” encoding that also has curly quotes and other niceties where ISO-8859-1 has unprintable control characters.
To see that MySQL really is storing the columns in different encodings you could do this:

mysql> select utf8,hex(utf8),hex(latin1) from test_encoding;
+------+-----------+-------------+
| utf8 | hex(utf8) | hex(latin1) |
+------+-----------+-------------+
| A    | 41        | 41          |
| Ñ    | C391      | D1          |
| Ŕ    | C594      | 3F          |
| ☺    | E298BA    | 3F          |
| €    | E282AC    | 80          |
+------+-----------+-------------+

What happens when we compare two columns encoded differently?

mysql> select utf8,latin1 from test_encoding where utf8=latin1;
+------+--------+
| utf8 | latin1 |
+------+--------+
| A    | A      |
| Ñ    | Ñ      |
| €    | €      |
+------+--------+

MySQL knows that the character repertoire of latin1 is a subset of utf8, so it converts the latin1 column into unicode so that it can be compared. If however you try to compare latin1 with latin2 this is what happens:

mysql> select latin1,latin2 from test_encoding where latin1=latin2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (latin2_bin,IMPLICIT) for operation '='

Since neither latin1 and latin2 is a subset of the other, MySQL cannot compare the columns. To be able to compare them you have to convert at least one to unicode, e.g. by using CONVERT(.. USING ..):

select latin1, latin2 from test_encoding
where convert(latin1 using utf8)=latin2;

I hope this information is enough to solve any character encoding problems you may have with MySQL once and for all.
(*) Did I say utf8 can store any Unicode characters? That’s not really true. MySQL’s utf8 and ucs2 only store unicode from the basic multilingual plane (BMP), which includes “only” the characters U+0000 through U+FFFF. If you have MySQL 5.5 or later you have additional encodings to choose from: utf16, utf32, utf8mb4. These encodings You can read the details, as usual, in the official MySQL documentation.

Resource: http://jonisalonen.com/2012/ultimate-guide-to-utf8-and-mysql/

Daily Dose of Drupal - Drupal Training Videos

2014-03-08T00:23:00.005-08:00

Daily Dose of Drupal - Drupal Training Videos

The Daily Dose of Drupal is a collection of Drupal training video tutorials containing all different types of Drupal related topics. We try to post a Drupal training video tutorial almost everyday (or at least as often as we can). The Drupal video topics will vary, but the basic idea is to provide step by step videos on how to do something in a short Drupal video. We try to keep it short, and try to cover a wide variety of topics. If you would like to see something a little different shoot us an email and let us know what Drupal topics you would like us to cover.
If you are interested in sponsoring an episode of the Daily Dose of Drupal, you can learn more on the CodeKarate sponsorship page.

Source: http://codekarate.com

WATable- - A swiss army jQuery table plugin Filter, format, paginate and sort your data with ease.

2014-02-25T22:19:00.001-08:00

WATable

- A swiss army jQuery table plugin
Filter, format, paginate and sort your data with ease. Give it a try!

Source: http://wootapa-watable.appspot.com/

Remote desktop sharing Ammyy Admin - download!

2013-10-15T22:44:00.004-07:00

Remote desktop sharing Ammyy Admin - download!

Ammyy Admin is free of charge for non-commercial use. You can download remote control application and start working with it within a few seconds.

Ammyy Admin - is a fast and easy way to gain remote sharing and remote desktop control!

Source: http://www.ammyy.com/en/downloads.html

Get list of existing TRIGGERs in MYSQL

2013-05-14T22:52:00.002-07:00

While MySQL has features like StoredProcedure and View, it doesn't have any option for displaying TRIGGER list in the 'Schemata' tab (right) section of QueryBrowser.

Generally, we need a third party control like MySQL Workbench to get the list of existing TRIGGER lists.But there is another way through which we can get existing trigger lists and that is from the Metadata(Data about data).

To get all the trigger list irrespective of the database name:

SELECT * FROM information_schema.TRIGGERS;

To get list of trigger against a specific database:

SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='database_name';

Source: http://www.mindfiresolutions.com/Get-list-of-existing-TRIGGERs-in-MYSQL-856.php

Howto: Performance Benchmarks a Webserver

2013-01-11T00:56:00.002-08:00

Apache Benchmark

You can benchmark Apache, IIS and other web server with apache benchmarking tool called ab. Recently I was asked to performance benchmarks for different web servers.
It is true that benchmarking a web server is not an easy task. From how to benchmark a web server:

First, benchmarking a web server is not an easy thing. To benchmark a web server the time it will take to give a page is not important: you don't care if a user can have his page in 0.1 ms or in 0.05 ms as nobody can have such delays on the Internet.

What is important is the average time it will take when you have a maximum number of users on your site simultaneously. Another important thing is how much more time it will take when there are 2 times more users: a server that take 2 times more for 2 times more users is better than another that take 4 times more for the same amount of users."
Here are few tips to carry out procedure along with an example:

Apache Benchmark Procedures

You need to use same hardware configuration and kernel (OS) for all tests
You need to use same network configuration. For example, use 100Mbps port for all tests
First record server load using top or uptime command
Take at least 3-5 readings and use the best result
After each test reboot the server and carry out test on next configuration (web server)
Again record server load using top or uptime command
Carry on test using static html/php files and dynamic pages
It also important to carry out test using the Non-KeepAlive and KeepAlive (the Keep-Alive extension to provide long-lived HTTP sessions, which allow multiple requests to be sent over the same TCP connection) features
Also don't forget to carry out test using fast-cgi and/or perl tests

Source : http://www.cyberciti.biz/tips/howto-performance-benchmarks-a-web-server.html#comments

Writing Object-Oriented JavaScript

2012-10-06T06:02:00.002-07:00

Introduction

ASP.NET and Visual Studio 7.0 are making important contributions to the improvement of the web development experience. Unfortunately, there is also a tendency created among developers to limit their interaction with JavaScript. Clearly JavaScript is valuable for adding client-side functionality to web pages. However ASP.NET programming models suggest that developers produce page layout while emitting client-side JavaScript from ASP.NET controls. As a consequence, this model tends to limit JavaScript to procedural adjuncts. This is rather unfortunate because it severely limits the power of an object-oriented scripting language that developers can use to write rich and reusable client-side components.

Source: http://www.codeproject.com/Articles/5608/Writing-Object-Oriented-JavaScript-Part-1
Another Source: http://mckoss.com/jscript/object.htm

Date Drop Down Lists

2012-09-20T01:17:00.003-07:00

This JQuery plugin converts a textfield with date value into a set of drop down lists for day, month and year.

Demo

When applied, the original textfield is replaced by a set of drop down lists, but it's value can still be referenced by the original ID:

Source: http://www.amdonnelly.co.uk/experiments/date-drop-down-lists.aspx

JQWidgets - Unique toolkit for HTML5 Web development

2012-09-14T05:11:00.004-07:00

jQWidgets gives us something new and special. It’s a new and unique library for Web development and unlike others it makes our Apps and Websites look beautiful on desktops, touch devices and mobile phones.
jQWidgets is the ultimate UI toolkit for the next generation of cloud apps development and it is built entirely on open standards and technologies like HTML5, CSS, JavaScript and jQuery.

jQWidgets brings broad platform and device coverage with minimum development cost. It is tested with many browsers and optimized to work well on popular phones and tablet devices as well as on PCs.

Source: http://www.jqwidgets.com/

Jquery Tutorial : Dom Manipulation

2012-09-04T05:33:00.001-07:00

3. DOM And Attributes Manipulation

To provide interaction on a page you want to be able manipulate elements dynamically.
In this chapter, you are going to learn how to dynamically:
- Create elements on the page
- Insert existing or freshly created elements on the page
- Move elements to a different location on the page
- Clone elements
- Remove elements
- Replace elements
From a selection of elements, you will be able to get and/or set their attribute values.

3.1. DOM Manipulation

Manipulating the DOM using jQuery is a common operation you will have to do most of time.
First we’ll focus on the different methods allowing you to create, insert, move, clone, remove, and replace elements in the DOM.

Source: http://marakana.com/bookshelf/jquery_tutorial/dom_manipulation.html

Little Web Hunt ( CSS Tutorials )

2012-07-22T05:05:00.001-07:00

HTML / XHTML - The language of the web

Reference

Video Tutorials

CSS - Cascading Style Sheets

Reference

Video Tutorials

Inkscape - Create Amazing Images

Video Tutorials

Filter Guide

GIMP - Image Manipulation

Video Tutorials

Source: http://www.littlewebhut.com/

HybridAuth, open source social sign on php library

2012-07-03T05:01:00.000-07:00

HybridAuth, open source social sign on php library

HybridAuth enable developers to easily build social applications to engage websites vistors and customers on a social level by implementing social signin, social sharing, users profiles, friends list, activities stream, status updates and more.

HybridAuth goal is to act as an abstract api between your application and various social apis and identities providers such as Facebook, Twitter, MySpace and Google.

Source: http://hybridauth.sourceforge.net/index.html

Nagesh Blog

Importing MySQL Databases via SSH

Port Forwarding Tester

How-to extract from mysqldump

101 Tips to MySQL Tuning and Optimization

MySQL Server Hardware and OS Tuning:

MySQL Configuration:

MySQL Schema Optimization:

MySQL Backup Procedures:

Installing Solr 4.6 on Windows

w2ui (New JavaScript UI Library)

w2ui (New JavaScript UI Library)

Popups and Overlays and many more

Database management MemCacheD General Usage

Search Engines: Comparison between Solr and Sphinx

Memcached Vs Redis, which one to pick for large web app?

Git Bash Common Command

MySQL - Character Sets and Collations

Setting MySQL default character set and collation in my.cnf

Checking current character set and collation for database/table/columns

For Database:

For Tables:

For Columns:

Converting character set and collations

MAKE BACKUP

Changing Database Character Sets and Collations

Changing Tables Character Sets and Collations

Changing Column Character Sets and Collations

TortoiseGit - git not found

Creating webservice server and client using NuSOAP

The Ultimate Guide To UTF-8 and MySQL

The Ultimate Guide To UTF-8 and MySQL

The truth about text in MySQL

I have everything set to utf8 and still the database gives me garbage!

What are the encodings in the tables good for, then?

Converting encodings

Practical demonstration

Daily Dose of Drupal - Drupal Training Videos

Daily Dose of Drupal - Drupal Training Videos

WATable- - A swiss army jQuery table plugin Filter, format, paginate and sort your data with ease.

WATable

- A swiss army jQuery table plugin Filter, format, paginate and sort your data with ease. Give it a try!

Remote desktop sharing Ammyy Admin - download!

Remote desktop sharing Ammyy Admin - download!

Get list of existing TRIGGERs in MYSQL

Howto: Performance Benchmarks a Webserver

Apache Benchmark

Apache Benchmark Procedures

Writing Object-Oriented JavaScript

Introduction

Date Drop Down Lists

Demo

JQWidgets - Unique toolkit for HTML5 Web development

Jquery Tutorial : Dom Manipulation

3. DOM And Attributes Manipulation

3.1. DOM Manipulation

Little Web Hunt ( CSS Tutorials )

HTML / XHTML - The language of the web

CSS - Cascading Style Sheets

Inkscape - Create Amazing Images

GIMP - Image Manipulation

HybridAuth, open source social sign on php library

HybridAuth, open source social sign on php library

I have everything set to `utf8` and still the database gives me garbage!

- A swiss army jQuery table plugin
Filter, format, paginate and sort your data with ease. Give it a try!