I have a SQL query, how can I create a CSV file from the SQL Query?
Solution
I had a customer looking for such an option. This can be easily achieved using ‘into outfile’ attribute of MySQL. Let me provide an example query like the following:
select * from wp_woocommerce_orders left join wp_posts on wp_posts.id = wp_woocommerce_orders.order_id into outfile '/var/lib/mysql/orders_list.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
The above query is joining the order table of woocommerce with the WordPress posts table, and putting the output file in /var/lib/mysql/orders_list.csv file, in which each field is being terminated by a comma (aka comma separated file) and after each row, it is terminating with a new line separator. One thing you need to remember is if you are not root, then you should provide the path to store the file in a place where you can write the file, for example, your home directory, which could be like /home/yourusername/something.csv
Hope this helps others to know how to achieve this.
Let’s say, you just installed two NVMe drives. That means, you currently have the following devices on your system:
/dev/nvme0n1
/dev/nvme0n2
Now, to use Raid 1 on these devices, you need to first partition them. If your devices are less than 2TB, you can use label msdos with fdisk. But I prefer gpt with parted. I will partition the disks using parted.
Open the disk nvme0n1 using parted
parted /dev/nvme0n1
Now, set the label to gpt
mklabel gpt
Now, create the primary partition
mkpart primary 0TB 1.9TB
Assuming 1.9TB is the size of your drive.
Run the above process for nvme1n1 as well. This will create one partition on each device which would be like the following:
/dev/nvme0n1p1
/dev/nvme1n1p1
Now, you may create the raid, using mdadm command as follows:
If you see, mdadm command not found, then you can install mdadm using the following:
yum install mdadm -y
Once done, you may now see your raid using the following command:
[root@bd3 ~]# cat /proc/mdstat
Personalities : [raid1]
md301 : active raid1 sdd1[1] sdc1[0]
976628736 blocks super 1.2 [2/2] [UU]
bitmap: 0/8 pages [0KB], 65536KB chunk
md201 : active raid1 nvme1n1p1[1] nvme0n1p1[0]
1875240960 blocks super 1.2 [2/2] [UU]
bitmap: 2/14 pages [8KB], 65536KB chunk
md124 : active raid1 sda5[0] sdb5[1]
1843209216 blocks super 1.2 [2/2] [UU]
bitmap: 4/14 pages [16KB], 65536KB chunk
md125 : active raid1 sda2[0] sdb2[1]
4193280 blocks super 1.2 [2/2] [UU]
md126 : active raid1 sdb3[1] sda3[0]
1047552 blocks super 1.2 [2/2] [UU]
bitmap: 0/1 pages [0KB], 65536KB chunk
md127 : active raid1 sda1[0] sdb1[1]
104856576 blocks super 1.2 [2/2] [UU]
bitmap: 1/1 pages [4KB], 65536KB chunk
unused devices: <none>
Here are a few key pieces of information about software raid:
It is better not to use Raid 10 with software raid. In case the raid configuration is lost, it is hard to know which drives were set as stripe and which like a mirror by the mdadm. It is a better practice to use raid 1 as a rule of thumb with software raid.
Raid 1 in mdadm doubles the read request in parallel. In raid 1, one request reads from one device, while the other request in parallel would read from the next device. This gives double read throughput when there is a parallel thread running. It still suffers from the write cost for writing data in two devices.
When someone tries to send you mail from gmail/outlook, they get a reply mail with the following errors:
550 Please turn on SMTP Authentication in your mail client. mail-lf1-f43.google.com [209.85.167.43]:42770 is not permitted to relay through this server without authentication.
Remote Server returned '550 5.7.368 Remote server returned authentication required to relay -> 550 Please turn on SMTP Authentication in your mail client. ;mail-oln040092255101.outbound.protection.outlook.com;(APC01-HK2-obe.outbound.protection.outlook.com) [40.92.255.101]:9693 is not;permitted to relay through this server without authentication.'
Error Solution
The error appears for several reasons. I will try to point out the most common ones I have faced for clients.
One, this domain is listed in /etc/remotedomains, while the domain is actually a local domain or shall use a local exchanger. As the domain is set not to use local mail exchange, hence the MTA is bouncing the mail back from receiving. To resolve this problem, you need to go to:
WHM >> DNS Zone Manager >> Search your domain that is having problem >> Manage
Now, click on the ‘Email Routing Configuration’ just beside the ‘Actions’ button as shown in the screenshot.
In the MX, make sure to set your local MX, and then select ‘Local Mail Exchanger’, then save.
If the domain was in remotedomains, and you did the above, and the issue is still not fixed, then you may want to run the following command to rebuild the remotedomains and localdomains file for Cpanel
/scripts/checkalldomainsmxs –yes
The second reason, the issue can appear, if you have defined two different MX and one of them, does not have the user, to whom the mail is trying to deliver. You should try to avoid such ambiguity. If you use multiple different MX, then you need to make sure, both of them accepts the same set of the user list.
When you try to open the terminal from Cpanel, it shows you an error in the red screen like the following:
The WebSocket handshake failed at 1:34:27 PM.
Solution
The error is appearing because Cpanel uses a socket to create a terminal window for you from the Cpanel. But this verifies the origin of your URL. If you are behind a proxy, then Cpanel won’t let the socket establish.
Most of the time, I have seen the user using Cloudflare behind the domain and using the same domain to access the Cpanel. Unfortunately, Cloudflare’s proxied IP won’t be able to create the websocket. Hence, you must use a domain or hostname or the server IP to access the Cpanel, to use Terminal.
Question: When I try to send mail using a PHP script in Cpanel, it automatically switches the SMTP hostname to localhost, even if I use a remote relay like sparkpost or mailgun. How to fix this?
Solution:
The reason behind the behavior is, by default Cpanel doesn’t let your customers use remote SMTP using PHP. This is controlled using a feature called ‘SMTP Restriction’. Go to WHM, log in using root, from Security Center select “SMTP Restrictions” and now, disable this. Your script shall now be able to send mail using remote SMTP like sparkpost/mailgun/pepipost.
Question: How can I see if the drives behind the hardware raid card using LSI has any reported error or not?
Solution
First, to find out if your drive raid arrays are optimal or not, you may run the following command:
[root@bd4 ~]# /opt/MegaRAID/MegaCli/MegaCli64 -LDInfo -Lall -aAll
Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name :dr1
RAID Level : Primary-1, Secondary-0, RAID Level Qualifier-0
Size : 931.0 GB
Sector Size : 512
Mirror Data : 931.0 GB
State : Optimal
Strip Size : 64 KB
Number Of Drives : 2
Span Depth : 1
Default Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy : Enabled
Encryption Type : None
Default Power Savings Policy: Controller Defined
Current Power Savings Policy: None
Can spin up in 1 minute: No
LD has drives that support T10 power conditions: No
LD's IO profile supports MAX power savings with cached writes: No
Bad Blocks Exist: No
Is VD Cached: No
Virtual Drive: 1 (Target Id: 1)
Name :
RAID Level : Primary-1, Secondary-0, RAID Level Qualifier-0
Size : 465.25 GB
Sector Size : 512
Mirror Data : 465.25 GB
State : Optimal
Strip Size : 64 KB
Number Of Drives : 2
Span Depth : 1
Default Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write Cache if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy : Enabled
Encryption Type : None
Default Power Savings Policy: Controller Defined
Current Power Savings Policy: None
Can spin up in 1 minute: Yes
LD has drives that support T10 power conditions: No
LD's IO profile supports MAX power savings with cached writes: No
Bad Blocks Exist: No
Is VD Cached: No
Exit Code: 0x00
This shall result in a key called ‘State’, which would say ‘Optimal’ if the raid is healthy. Although, it is possible that your drives have reported a few errors which might indicate a potential drive failure, which hasn’t been picked up by the RAID state yet. These errors are available under the following command:
/opt/MegaRAID/MegaCli/MegaCli64 pdlist a0
The above command lists the drive details. There are 3 error/failure counts, which are important to notice are ‘Media Error Count’, ‘Other Error Count’, and ‘Predictive Failure Count’. If you are seeing the number is changing quickly a few sets of times, then you should look at the drive status closely, as it seems to be producing a hardware failure soon. I have seen several times in my life, that the raid state saying it is ‘Optimal’, but the Media error was reported, soon after, we found the drive was actually failing.
To find out error counts in one go, you may use the following:
Installing Odoo 15 along with the CentOS 7 and the latest PGSQL repo has changed pretty a lot. I will try to cover solutions to a few errors along with the straightforward steps on installing Odoo 15 in CentOS 7.
First Step First
Update your CentOS 7 installation and install Epel-release
yum update -y
yum install epel-release
Install Python 3.8
We will use Python 3.8 for Odoo 15. We will use Software Collection Repository or SCL to install our Python binary. You may find details of SCL here:
Once done, you can now install Python 3.8 using the following:
yum install rh-python38 -y
Also, install python38-devel as Python.h is used to compile psycopg2 and python-ldap package. From Odoo 15, you need this to get going:
yum install rh-python38-python-devel -y
Note: The above is used to resolve an error like the following
fatal error: Python.h: No such file or directory
Now, we will install a few prerequisites to install Odoo 15. One difference between the old version installed and the new is that you need to load GCC-c+ now along with the GCC compiler. Otherwise, you will see an error like the following:
gcc: error trying to exec ‘cc1plus’: execvp: No such file or directory
So, to install the pre-requisites, run the following:
Once done, now can you initiate the PostgreSQL and start the database server
/usr/pgsql-13/bin/postgresql-13-setup initdb
systemctl start postgresql-13.service
systemctl enable postgresql-13.service
# create the postgres user odoo
su - postgres -c "createuser -s odoo"
Brilliant, now, one more additional thing we need to resolve. With the latest Postgresql 13, you might still not be able to use the libpq. You need to install it manually. Otherwise, you will see an error like the following:
fatal error: libpq-fe.h: No such file or directory
To resolve this error, you need to install these libraries manually with the following command:
yum install libpq5 libpq5-devel -y
Remember to install libpq5-devel as the source of the libpq would be used to compile psycopg2.
Install Wkhtmltox
Now, let’s move to the next step of installing wkhtmltox. The version for wkhtmltox has remained the same for pretty long. The following shall work till now:
cd /opt
wget https://github.com/wkhtmltopdf/packaging/releases/download/0.12.6-1/wkhtmltox-0.12.6-1.centos7.x86_64.rpm
yum localinstall wkhtmltox-0.12.6-1.centos7.x86_64.rpm
This specific tool is used to generate reports in Odoo, without this, you might not be able to use pdf/html reports using Qweb in Odoo.
Final Step: Install and Configure Odoo 15
We will here download the source from Github and install all the dependent packages. First, we switch to the user odoo
su - odoo
Now, clone the git repo for Odoo 15 to a folder odoo15 using the following:
Once done, now, we can enable python3.8 and create a virtual environment for our Odoo installation. First, enable the Python3.8 using scl:
scl enable rh-python38 bash
Now create a virtual environment for our Odoo15 installation:
cd /opt/odoo
python3 -m venv odoo15-venv
Activate the virtual environment we just created
source odoo15-venv/bin/activate
Now, we upgrade the pip and install wheel package:
pip install --upgrade pip
pip3 install wheel
Now, before we can install the requirements file using pip3 package installer, here is an error you will face when compiling psycopg2
Error: pg_config executable not found.
Now the problem is understandable, pg_config file is usually placed under the binary folder of pgsql which is:
/usr/pgsql-13/bin
For some reason, our installer fails to identify this. To solve the issue, we first, load this in the $PATH variable before running pip3 for requirements.
export PATH=/usr/pgsql-13/bin/:$PATH
Now, you can run the pip3 installer:
pip3 install -r odoo15/requirements.txt
This shall be complete without any error if you have solved the solutions I had given above. If any of them are missed, you should double-check all the mentioned errors above.
Now exit the venv:
deactivate && exit ; exit
Now, the first step for configuration, edit the /etc/odoo.conf file
nano /etc/odoo.conf
Paste the following:
[options]
; This is the password that allows database operations:
admin_passwd = set_the_password_to_create_odoo_database
db_host = False
db_port = False
db_user = odoo
db_password = False
addons_path = /opt/odoo/odoo15/addons
; You can enable log file with uncommenting the next line
; logfile = /var/log/odoo15/odoo.log
Replace ‘set_the_password_to_create_odoo_database’ with the one you want to use to allow odoo installer to create the database for you.
Odoo15 Service File
Now, we will create a service file to start/stop/restart our Odoo 15 installation.
Unable to connect to any wifi with NetworkManager due to error: Secrets were required, but not provided
The reason the above error is showing is that NetworkManager is trying to reuse an existing connection, which shouldn’t happen. You need to delete the connection first.
You might see the following set of errors with Cpanel
When trying to remove the redirect from Cpanel
Apache detected an error in the Rewrite config. <pre>httpd_ls_bak: Syntax error in -C/-c directive: Include/IncludeOptional: Could not open directory /usr/local/apache/conf.modules.d: No such file or directory </pre> Please try again.
/scripts/rebuildhttpdconf generates an error like the following:
httpd: Syntax error in -C/-c directive: Include/IncludeOptional: Could not open directory /usr/local/apache/conf.modules.d: No such file or directory
Resolution
Previously, we reinstalled apache24 to solve the issue like the following:
yum reinstall ea-apache24
Although you may also reinstall the apache config runtime extension, that shall fix the issue as well.
Make sure to replace ‘/home/yourdomain.com/public_html/yourdomain.com/’ with the path of your home directory and ‘yourdomain.com’ with the original domain. This will show something like the following once the retrieve is done
IMPORTANT NOTES:
- Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/yourdomain.com-0003/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/yourdomain.com-0003/privkey.pem
Your cert will expire on 2022-03-12. To obtain a new or tweaked
version of this certificate in the future, simply run certbot
again. To non-interactively renew *all* of your certificates, run
"certbot renew"
The noticeable thing is the following:
/etc/letsencrypt/live/yourdomain.com-0003/
This ends with 003 because this is the 3rd time, it is getting a certificate from Let’s Encrypt for the same domain.
Now, first go to /etc/letsencrypt/live and change this folder to yourdomain.com, and then restart lsws like the following:
cd /etc/letsencrypt/live
mv yourdomain.com yourdomain.com_old_1
mv yourdomain.com-003 yourdomain.com
service lsws restart
