Tuesday, November 18, 2014

Magento - Category Reset

So I have a magento install that needed to be cleared out. One of the problems is resetting the categories back to zero.  The issue is that the admin area uses these categories also.

Solution: Don't delete the #1 entity_id.

MySQL Queries...
DELETE FROM catalog_category_entity where entity_id <> 1;
ALTER TABLE catalog_category_entity AUTO_INCREMENT = 2;

DELETE FROM catalog_category_entity_datetime where entity_id <> 1;
ALTER TABLE catalog_category_entity_datetime AUTO_INCREMENT = 2;

DELETE FROM catalog_category_entity_decimal where entity_id <> 1;
ALTER TABLE catalog_category_entity_decimal AUTO_INCREMENT = 2;

DELETE FROM catalog_category_entity_int where entity_id <> 1;
ALTER TABLE catalog_category_entity_int AUTO_INCREMENT = 2;

DELETE FROM catalog_category_entity_text where entity_id <> 1;
ALTER TABLE catalog_category_entity_text AUTO_INCREMENT = 2;

DELETE FROM catalog_category_entity_varchar where entity_id <> 1;
ALTER TABLE catalog_category_entity_varchar AUTO_INCREMENT = 2;

Friday, November 14, 2014

Install SB235 bluetooth headset on xubuntu


Thanks to Warreee I was able to get these connected to xubuntu.  The piece I was missing was installing the paulsaudio-module-bluetooth.


If you're getting the following error while setting up a bluetooth headset on Xubuntu with PulseAudio:Bluetooth Audio Sink: Stream Setup Failed then you probably will need to do the following:
Install the following: sudo apt-get install pulseaudio-module-bluetooth
And add the following command to your Startup and Session: pactl load-module module-bluetooth-discover.
Enjoy!



Then, in PulseAudio Volume Control,  I just needed to set the device to SB235 on Chromium under playback and under Output Devices.





Find all auto_increment tables

MySQL Information Schema to the rescue.

I needed to find all auto_increment tables.

SELECT 
    * 
FROM 
    `information_schema`.`COLUMNS`
WHERE 
    `EXTRA` = 'auto_increment' AND 
    `TABLE_SCHEMA` = 'foochoo'

Now I need to make all these tables a larger auto_increment value.

use information_schema;
select table_name 
from tables 
where auto_increment is not null and table_schema=...;

You can then set the auto-increment value as per Change auto increment starting number?
Or, in a single shot (assuming Unix shell):

mysql information_schema -e 
'select concat ("ALTER TABLE ",table_name," AUTO_INCREMENT=1000000") `-- sql` 
from tables 
where auto_increment is not null and table_schema="your-schema";
'|mysql your-schema

Wednesday, November 12, 2014

mysqldump with INSERT … ON DUPLICATE

Thanks to stackoverflow for another helpful mysqldump tidbit. This logic will allow you to do updates to your database via a mysqldump. Similar to querying with...
  
  create table db1.table1 like db2.table1;
  insert into db1.table1 select a,b,c from db2.table1 as db2t1 ON DUPLICATE KEY UPDATE a=db2t1.a,b=db2t1.b,c=db2t1.c;
posted by RolandoMySQLDBA
  
  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.
  
Keep this paradigm in mind

mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using --replace (or --insert-ignore) and --no-create-info into DUMP2
load DUMP2 into DB3

Tuesday, November 11, 2014

Centos 7 Server LAMP Stack.

This is really just notes for a server install, but maybe it will help someone.

start with standard Centos 7 Server Install
#hostnames!!

/etc/hostname
vi /etc/hosts

### network config ###

#vmware... if using vmware, make sure the "hardware" is on the right network.
VM Network ##

# ifcfg...
vi /etc/sysconfig/network-scripts/ifcfg-eno...
---- EXAMPLE
#HWADDR
IPV4_FAILUER_FATAL=yes
#IPV6...
#UUID
---- /EXAMPLE

# restart machine (CentOS 7 requires this)
---------After this point you can ssh into the box.----------
ifconfig

#In CentOS 7.0 uses Firewall-cmd, so I will customize it to allow external access to port 80 (http) and 443 (https) and 3306 (mysql).
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --permanent --zone=public --add-service=mysql
firewall-cmd --reload

## php / apache
yum install httpd rsync php php-gd php-mysql php-xml php-xmlrpc php-common php-cli php-xml php-mbstring php-gd php-soap wget git rsync mariadb



### Now configure your system to start Apache at boot time...
systemctl start httpd.service
systemctl enable httpd.service
curl http://icanhazip.com
systemctl restart httpd.service


### mcrypt
cd /tmp/ && wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-2.noarch.rpm
rpm -ivh epel-release-7-2.noarch.rpm
yum --enablerepo=epel install php-mcrypt

### PHP INI
# vi /etc/php.ini
# memory_limit = 512M

### SELINUX
# vi /etc/sysconfig/selinux
# SELINUX=disabled

### APC
yum install php-pear php-devel httpd-devel pcre-devel gcc make
pecl install apc
echo "extension=apc.so" > /etc/php.d/apc.ini
cp /usr/share/pear/apc.php /var/www/html/
#  Now set the username and password in the file apc.php as shown below.
pecl upgrade apc
vi /etc/php.d/apc.ini
----- PASTE
apc.enabled = 1
apc.optimization  = 0
apc.shm_segments = 1
apc.shm_size = 768M
apc.ttl = 48000
apc.user_ttl  = 48000
apc.num_files_hint = 8096
apc.user_entries_hint = 8096
apc.mmap_file_mask = /tmp/apc.XXXXXX
apc.enable_cli = 1
apc.cache_by_default  = 1
apc.max_file_size = 10M
apc.include_once_override = 0
------ /PASTE

# ssh keys
## on server - setup ssh keys.
ssh-keygen -t dsa
chmod -R 600 ~/.ssh
## local - push key to server
cat ~/.ssh/id_dsa.pub | ssh root@mage.izon.usi.ben 'cat - >>~/.ssh/authorized_keys'

# setup virtual hosts in /etc/httpd/conf.d/hostnamex.conf
------  Example
<VirtualHost *:80>
    AllowEncodedSlashes On

    ServerName test.com
    ServerAlias 192.168.0.100
    ServerAlias www.test.com
    ServerAdmin admin@test.com
    DocumentRoot /var/www/html/

    DirectoryIndex index.html

    <Directory "/var/www/html/" >
        AllowOverride All
    </Directory>
</VirtualHost>
------ /Example

systemctl restart httpd.service

# mysql!! http://sharadchhetri.com/2014/07/31/how-to-install-mysql-server-5-6-on-centos-7-rhel-7/
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
# so you just added 2 new yum repos.
yum install mysql-server
systemctl start mysqld

# finish installation (use blank for initial password)
mysql_secure_installation

# recommend removing everything for testing.
 -- annonymous, remote login, test db
systemctl enable mysqld.service

# if not installing mysql server... just use the mariadb client. (same thing)
yum install mariadb

yum -y install nfs-utils inkscape

yum -y install htop zsh
curl -L http://install.ohmyz.sh | sh
# edit .zshrc , us theme ys, add more if you like...

############
# Preferred editor for local and remote sessions
if [[ -n $SSH_CONNECTION ]]; then
  export EDITOR='vim'
else
  export EDITOR='mvim'
fi

h=()
if [[ -r ~/.ssh/config ]]; then
  h=($h ${${${(@M)${(f)"$(cat ~/.ssh/config)"}:#Host *}#Host }:#*[*?]*})
fi
if [[ -r ~/.ssh/known_hosts ]]; then
  h=($h ${${${(f)"$(cat ~/.ssh/known_hosts{,2} || true)"}%%\ *}%%,*}) 2>/dev/null
fi
if [[ $#h -gt 0 ]]; then
  zstyle ':completion:*:ssh:*' hosts $h
  zstyle ':completion:*:slogin:*' hosts $h
fi

source $HOME/.aliases

function git_prompt_info() {
  ref=$(git symbolic-ref HEAD 2> /dev/null) || return
  echo "$ZSH_THEME_GIT_PROMPT_PREFIX${ref#refs/heads/}$ZSH_THEME_GIT_PROMPT_SUFFIX"
}
###################


# mount remote code share... (nfs)
vi /etc/fstab
# add this line...
xxx.yyyy.sss.com:/exports/public          /var/www/vhosts         nfs     defaults        0 0
# not mount the directory.
mkdir /var/www/vhosts
mount /var/www/vhosts

# /etc/idmapd.conf - to get permissions from nfs share.
set Domain = xxx.yyyy.sss.com

# link to version to html directory.
rm -rf /var/www/html
ln -s /var/www/vhosts/v1 /var/www/html


### mysql access user..
CREATE USER 'username'@'localhost' IDENTIFIED BY PASSWORD '*hashedpasswordvalue';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;


vi /etc/my.cnf
###### after ....
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
###### add ....

wait_timeout = 28800
interactive_timeout = 28800
max_allowed_packet=128M

read_rnd_buffer_size = 12M
sort_buffer_size = 12M

innodb_buffer_pool_size = 1G
key_buffer_size = 512MB

innodb_table_locks=0
autocommit=1

# Size this cache to keep most tables open since opening tables can be expensive.
# The optimum value for table_cache is directly related to the number of tables
# that need to be opened simultaneously in order to perform multiple-table joins.
# The table_cache value should be no less than the number of concurrent connections
# times the largest number tables involved in any one join.
# You should check the Open_tables status variable to see if it is large compared to table_cache
table_open_cache=10000

tmp_table_size=512M
max_heap_table_size=512M
thread_cache=16
secure-auth=0

# logging
log-queries-not-using-indexes = 1
long_query_time = 10
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow_query_log


innodb_force_recovery = 0
# Avoid double buffering
innodb_flush_method = O_DIRECT

# infinite threads - http://www.mysqlperformanceblog.com/2011/12/02/kernel_mutex-problem-cont-or-triple-your-throughput/
innodb_thread_concurrency=8

innodb_log_files_in_group=6
innodb_log_file_size=512M
innodb_log_buffer_size=16M
# breaks ACID, can loose 1-2 seconds of data on OS crash, but much greater performance
innodb_flush_log_at_trx_commit=0

# locks that did not match the scan are released after the STATEMENT completes.
# prevents gap locks (write locks) on the table
transaction-isolation=READ-COMMITTED

max_connections=3000
# Check it however after a while and see if it is well used
query_cache_size=50M
query_cache_limit = 10M
skip-name-resolve=1



###################################
# MyISAM stuff
###################################
# http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/
read_buffer_size = 2M
read_rnd_buffer_size = 12M
myisam_sort_buffer_size = 256M
thread_cache_size = 12
join_buffer_size = 4M
thread_concurrency = 0 # specific to solaris
ft_min_word_len = 3

###################################

Computer Theory - Need for speed.

I am frustrated right now that my computer is not processing fast enough.  Who cares if it is 3 years old? Who cares if I am on a VPN and SSHing into a office machine (2 miles away), in turn SSHing into another machine in another location (150 miles away). Latency? BAH!

ME: if i stare at the computer or hit it, then it should go faster.... right?
ZZ: lol, I found throwing it makes it go the fastest :P

Server Admin humor, excellent.  

And I am spent, damn it.

Monday, November 3, 2014

move all files in current directory into a subdirectory in the current directory

I needed to move all the files in the current working directory (mount point) into a sub-directory. I hope this helps others, and me in the future.

Use the -maxdepth 1 option to find, instead of the "*" and ".*", something like this:

Code:
mkdir ./dest_dir
find . -maxdepth 1 | grep -v dest_dir | xargs -i mv {} ./dest_dir
http://www.linuxquestions.org/questions/linux-newbie-8/move-all-files-in-current-directory-into-a-subdirectory-in-the-current-directory-637150/


  -i,--replace=[R]             Replace R in initial arguments with names
                               read from standard input. If R is
                               unspecified, assume {}

http://community.spiceworks.com/how_to/show/98496-move-all-files-in-current-directory-into-a-subdirectory-in-the-current-directory

---------

addition per spiceworks! (Leecallen)

Nice, and useful.
Slight simplification - explicitly tell find that you are only interested in files, and then remove the grep command:

     find . -maxdepth 1 -type f | xargs -i mv {} ./dest_dir;

or just for kicks:

     find . -maxdepth 1 -type f -exec mv {} ./dest_dir \;