Archive

Archive for the ‘MySQL’ Category

SQL cheatsheet.

February 26, 2012 Leave a comment

My sql

SELECT x.a
FROM
( SELECT ’1′ AS a
UNION ALL
SELECT ’2′
UNION ALL
SELECT ’3′
) x
WHERE a NOT IN
( SELECT knr FROM table1 )

SELECT t.CHANNEL
, COUNT(*) / SUM(grp.TotalCount)
FROM my_table t
JOIN
( SELECT LOC_CODE
, COUNT(*) TotalCount — or is it perhaps?:
— COUNT(DISTINCT CHANNEL)
FROM my_table
GROUP BY LOC_CODE
) grp
ON grp.LOC_CODE = t.LOC_CODE
GROUP BY t.CHANNEL

My delete:

DELETE
FROM table
WHERE id IN
( SELECT id
FROM table
WHERE obsolete = 1
ORDER BY id
FETCH FIRST 100000 ROWS ONLY
)

Categories: MySQL

Tizen Linux for mobile tablet smartphone will takeover the world. Backed by Intel, Samsung, Linux foundation. Good luck.

February 26, 2012 Leave a comment

Tizen Linux for mobile tablet smartphone will takeover the world. Backed by Intel, Samsung, Linux foundation. Good luck.

- Android is less important in such case

- IPhone also

 

MySQL is 70x faster then before.

February 26, 2012 Leave a comment

MySQL cluster has 70x speed.

Categories: MySQL, Unix/Linux

How to create a database without any database server?

August 5, 2011 Leave a comment

@todo

Please ask, i will post how to’s

Categories: MySQL, Oracle, PHP, Zend

MySQL – backup details stored procedure all including database

June 4, 2010 Leave a comment

To backup the mysql database

No lose all backup, case of migration:
mysqldump –user=root –password=**** –all-databases > /tmp/dnbe_database.sql

Option 1: full database and functions structures but no data
shell> mysqldump –user=root –password=root -R -d db > /tmp/20090201-nodata.sql

Option 2: full database but without cdr, slavecdr table structure and data
shell> mysqldump –user=root –password=root -R –ignore-table=db.cdr –ignore-table=db.slavecdr db > /tmp/20090201-data.sql

Categories: MySQL

Mysql ERROR 1153 (08S01) Got a packet bigger than ‘max_allowed_packet’ bytes

April 18, 2010 Leave a comment

That error while recovering the database file from already backup file.

1.

mysql> show variables;

| max_allowed_packet              | 1048576                                                    |

2. vi /etc/my.cnf

[mysqld]
max_allowed_packet=16M

3. service mysqld restart

4. mysql –user=root –password= < /tmp/myfailedfile.sql

Done.

Categories: MySQL

zend cheat sheet zend framework cheat sheet zf cheat sheet zf cheat code zend crack zend hack zend quick tips and tricks

April 11, 2010 1 comment

Zend framework Latest Release. Also for more: http://shamuntoha.wordpress.com/2011/04/25/zend-framework-cheat-sheets-poster-tutorial-download-latest-news-reference-guide-apis-training/

*********Please leave your comments with your requirements and demands.********
zend framework poster zend framework cheat code cheat sheet tutorial getting started

  • Het Zend Framework is een open-source framework, ontwikkeld in objectgeoriënteerde PHP5 code door Zend Technologies
  • Zend Framework is an open source, object oriented web application framework
  • Free download of Zend Framework. Download today!
  • Official website www.zend.com . Includes project news, roadmap, FAQ and manual.
  • 10 Compelling Reasons to Use Zend Framework | Nettuts+

  • Latest version of Zend framework- ZF 1.6.0 ship dojo toolkit. You can find it in ZendFramework-1.6.0/external/ directory when you download ZF

Het Zend Framework is een open-source framework, ontwikkeld in objectgeoriënteerde PHP5 code door Zend Technologies. Het framework is gemaakt met de gedachte om het gebruik ervan zo simpel mogelijk te houden. Dit in lijn met PHP, waarvoor de parsing engine ook door Zend Technologies wordt ontwikkeld. Het Zend Framework wordt uitgebracht onder een BSD-licentie.

Het is onderdeel van het PHP Collaboration Project, bestaande uit:

* Zend Developer Zone
* Eclipse-based PHP-IDE
* Zend Framework

Het framework is gebaseerd op het MVC Model. De verschillende componenten van het framework kunnen ook afzonderlijk gebruikt worden. Voorbeelden van componenten in het Zend Framework zijn Zend_Log, Zend_Config en Zend_Feed.
Toekomst

Het project heeft een zestal doelen gesteld voor de toekomst:

1. Meer webservices ondersteunen
2. Mogelijkheden voor identiteitsmanagement
3. Verregaande mogelijkheden met betrekking tot formulieren
4. Ondersteuning voor YAML als informatie-opmaaktaal
5. Ontwikkelingstools om het ontwikkelen te vergemakkelijken
6. Het continu uitbreiden en verbeteren van functionaliteit

Externe links

* Homepage Zend framework
* Zend Developer Zone
* Het gebruik van Zend componenten in CakePHP

Programmer’s Reference Guide

Table of Contents

Zend Framework

Programmer’s Reference Guide for Zend Framework

Centos oracle oracle10g learn oracle quick install oracle linux getting started centos oracle

April 11, 2010 3 comments

* GO CentOS 5.4 (enterprise linux get it)
* Oracle get it: http://www.oracle.com/technology/software/products/database/xe/htdocs/102xelinsoft.html

1. installing oracle 10g

[root@www Desktop]# <strong>yum --nogpg localinstall oracle-xe-10.2.0.1-1.0.i386.rpm</strong>
Loaded plugins: fastestmirror
Setting up Local Package Process
Examining oracle-xe-10.2.0.1-1.0.i386.rpm: oracle-xe-10.2.0.1-1.0.i386
Marking oracle-xe-10.2.0.1-1.0.i386.rpm to be installed
Loading mirror speeds from cached hostfile
* addons: ftp.belnet.be
* atomic: www5.atomicorp.com
* atomic-testing: www5.atomicorp.com
* base: ftp.belnet.be
* contrib: ftp.belnet.be
* extras: ftp.belnet.be
* rpmforge: fr2.rpmfind.net
* updates: ftp.belnet.be
Resolving Dependencies
--> Running transaction check
---> Package oracle-xe.i386 0:10.2.0.1-1.0 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================
Package         Arch       Version              Repository                          Size
==========================================================================================
Installing:
oracle-xe       i386       10.2.0.1-1.0         /oracle-xe-10.2.0.1-1.0.i386       380 M

Transaction Summary
==========================================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total size: 380 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing     : oracle-xe                                                          1/1
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

Installed:
oracle-xe.i386 0:10.2.0.1-1.0

Complete!

2. /etc/init.d/oracle-xe configure


[root@www Desktop]# <strong>/etc/init.d/oracle-xe configure</strong>

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:<strong>8080</strong>

Specify a port that will be used for the database listener [1521]:<strong>1521</strong>

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
<strong>initial configuration:
Confirm the password:</strong>

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:<strong>y</strong>

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
[root@www Desktop]#

3. Open it in firefox browser: http://127.0.0.1:8080/apex

Done

*go oracle on fedora/centos.

Zend zend_db sql mysql

March 2, 2010 Leave a comment

How to zend sql?

1. Free hand SQL, make it FREE!

<?php

class IndexController extends Zend_Controller_Action

{

public function init()

{

// Instant db access

$db = Zend_Db::factory(‘Pdo_Mysql’,

array(

‘host’ => ‘localhost’,

‘username’ => ‘root’,

‘password’ => ’00000′,

‘dbname’ => ‘myzf’

));

Read more…

centOS – Postfix + Postfixadmin + Dovecot

January 4, 2010 4 comments

Postfixadmin Installation:

        [root@www /]# uname -a
        Linux www..be 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x8 6_64 x86_64 GNU/Linux

1. google download postfixadmin

2. tar xvfz postfixadmin_2.3.tar.gz.tar.gz -C /usr/share/

3. yum install php-imap.i686
        * restart the httpd -k restart

4. vi /usr/share/postfixadmin-2.3/config.inc.php

        config.$CONF['configured'] = true;
        // Database Config
        $CONF['database_type']     = ‘mysql’;
        $CONF['database_host']     = ‘localhost’;
        $CONF['database_user']     = ‘root’;
        $CONF['database_password'] =;
        $CONF['database_name']     = ‘postfix’;
        // Encrypt
        $CONF['encrypt'] = ‘cleartext’; // shamun
        
        [SAVE]

5. http://localhost/postfixadmin/setup.php



6. when you make setup password, copy the given line and past in the following file.
        vi /usr/share/postfixadmin-2.3/config.inc.php
        $CONF['setup_password'] = 'b90d3eceb6e475e85e92b04eb5d6cbd1:e80d6fa140cb7ea3a65783e16e7afc3ebdaa712f';

7. you make admin account thats it.

Note important:

8. make a virtual domain like i did: shamun.be

9. make a virtual email like marie@shamun.be

- this will be needed for a test emails, at the end

Postfix Installation:

1. Add user
        [root@www ~]# useradd vmail
        [root@www ~]# id vmail
        uid=501(vmail) gid=501(vmail) groups=501(vmail) context=user_u:system_r:unconfined_t
        [root@www ~]#

2. Install postfix with mysql support (default is without mysql)
        [root@www etc]# vi /etc/yum.repos.d/CentOS-Base.repo
        
        #[base] and [update] sections of /etc/yum.repos.d/CentOS-Base.repo added
        exclude=postfix-*
        
        [centosplus]
        name=CentOS-$releasever - Plus
        mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
        #baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
        gpgcheck=1
        enabled=1
        gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-centos4
        includepkgs=postfix-*
        
        [root@www ~]# yum install postfix
        Installed:
                postfix.x86_64 2:2.3.3-2.1.centos.mysql_pgsql
        
        [root@www postfix]# postconf -m
        btree
        cidr
        environ
        hash
        ldap
        mysql
        nis
        pcre
        pgsql
        proxy
        regexp
        static
        unix
        
        [root@www ~]# whereis postfix
        postfix: /usr/sbin/postfix /etc/postfix /usr/libexec/postfix /usr/share/man/man1/postfix.1.gz
        

3. vi /etc/postfix/main.cf
        # My edits
        virtual_mailbox_domains = mysql:/etc/postfix/mysql-domains.cf
        virtual_mailbox_maps = mysql:/etc/postfix/mysql-users.cf
        virtual_alias_maps = mysql:/etc/postfix/mysql-aliases.cf
        virtual_mailbox_base = /home/vmail
        virtual_uid_maps = static:501
        virtual_gid_maps = static:501
# [optional] but important in production:
# important, this part of postfix is for incoming emails to receive
# when i send email from hellobello@gmail.com to this@setup.com
# Email bounced and gets fail: The error that the other server returned was: 550 550 5.1.1
# So, put this as following
local_recipient_maps = # leave empty incase you want public and local test get success
inet_interfaces = all # instead of localhost

#[optional] - spamm protect
# Spamm kill
permit_mynetwork= $mynetworks
check_relay_domains = $relay_domains, $inet_interfaces
smtpd_helo_required = yes
smtpd_recipient_restrictions =
 permit_mynetworks,
 check_client_access hash:/etc/postfix/clients,
 reject_rbl_client cbl.abuseat.org,
 reject_rbl_client pbl.spamhaus.org,
 reject_rbl_client sbl.spamhaus.org,
 reject_unauth_destination

# test local mail to outside
# test outside mail to inside
# (Test this from shell, that nobody using your box sending emails): telnet relay-test.mail-abuse.org

4. vi /etc/postfix/mysql-domains.cf
# config

host = localhost

user = root

password =

dbname = postfix

table = domain

select_field = domain

where_field = domain

additional_conditions = and backupmx = '0' and active = '1'

#

# Side work - postfixadmin

# add domains ex: mydomain.com

# add email address ex: me@mydomain.com

#
5. Test point 4

[root@www postfix]# postmap -q yourdomain.com mysql:/etc/postfix/mysql-domains.cf

yourdomain.com
6. vi /etc/postfix/mysql-users.cf

host = localhost

user = root

password =

dbname = postfix

table = mailbox

select_field = maildir

where_field = username

additional_conditions = and active = '1'

result_format = %sMaildir/
7. Test point 6

[root@www postfix]# postmap -q support@yourdomain.com mysql:/etc/postfix/mysql-users.cf

support@yourdomain.com/Maildir/

[root@www postfix]#
8. start postfix

[root@www postfix]# postfix reload

[root@www postfix]# service postfix restart

Shutting down postfix: [FAILED]

Starting postfix: [ OK ]

[root@www postfix]#
9. Send mail
[root@www postfix]# telnet localhost smtp

Trying 127.0.0.1...

Connected to localhost.localdomain (127.0.0.1).

Escape character is '^]'.

220 www.yourdomain.com ESMTP Postfix

ehlo host

250-www.yourdomain.com

250-PIPELINING

250-SIZE 10240000

250-VRFY

250-ETRN

250-ENHANCEDSTATUSCODES

250-8BITMIME

250 DSN

mail from: support@yourdomain.com

250 2.1.0 Ok

rcpt to: webmaster@yourdomain.com

250 2.1.5 Ok

data

354 End data with <CR><LF>.<CR><LF>

Dear Marie,

How are you? Alles goed?

Mvg

Shamun

.

250 2.0.0 Ok: queued as 4E20D5F88033

quit

221 2.0.0 Bye

Connection closed by foreign host.

[root@www postfix]#
[root@www home]# tree

.

|-- shamun

`-- vmail

`-- webmaster@yourdomain.com

`-- Maildir

|-- cur

|-- new

| `-- 1269444409.Vfd00I13d0017M216619.www.yourdomain.com

`-- tmp

7 directories, 1 file

[root@www home]#
Dovecot Installation:
1. Install dovecot (dovecot-mysql not need in centos 5.4)

[root@www home]# yum install dovecot.x86_64

Installed:

dovecot.x86_64 0:1.0.7-7.el5
2. Edit

[root@www home]# whereis dovecot

dovecot: /usr/sbin/dovecot /etc/dovecot.conf /usr/libexec/dovecot

[root@www home]# vi /etc/dovecot.conf

# Protocols we want to be serving: imap imaps pop3 pop3s

# If you only want to use dovecot-auth, you can set this to "none".

protocols = pop3 pop3s imap imaps

##

## Mailbox locations and namespaces

##

mail_location = maildir:~/Maildir/

##

## IMAP specific settings

##

protocol imap {

imap_client_workarounds = delay-newmail outlook-idle netscape-eoh

}

##

## POP3 specific settings

##

protocol pop3 {

pop3_uidl_format = %08Xu%08Xv

pop3_client_workarounds = outlook-no-nuls oe-ns-eoh

}

##

## Authentication processes

##

# For 64-bit users: Add the line login_process_size = 64 in the file /etc/dovecot.conf.

auth_username_format = %Lu

auth default {

passdb sql {

args = /etc/dovecot-mysql.conf

}

userdb static {

#args = uid=501 gid=501 home=/home/vmail/%d/%n

#/home/vmail/shamun.be/marie/Maildir/new

args = uid=501 gid=501 home=/home/vmail/%u

#/home/vmail/marie@shamun.be/Maildir/new i am using this way!

}

}


3. vi /etc/dovecot-mysql.conf

driver = mysql

connect = host=localhost dbname=postfix user=root password=

default_pass_scheme = PLAIN

password_query = SELECT password from mailbox where username = '%u'


4. Start dovecot

[root@www home]# service dovecot restart

Stopping Dovecot Imap: [FAILED]

Starting Dovecot Imap: [ OK ]
5. Test

[root@www home]# telnet localhost pop3

Trying 127.0.0.1...

Connected to localhost.localdomain (127.0.0.1).

Escape character is '^]'.

+OK Dovecot ready.

user webmaster@example.com

+OK

pass xxxxxxxxxx

+OK Logged in.

list

+OK 1 messages:

1 520

.

quit

+OK Logging out.

Connection closed by foreign host.

[root@www home]#

Categories: centOS, Fedora 12, MySQL, Unix/Linux Tags:
Follow

Get every new post delivered to your Inbox.