MARKS TUTORIAL

Identifier Case Sensitivity  In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is mac-OS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, mac-OS also supports UFS volumes, which are case-sensitive just as on any Unix. The  lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section. Recommendation : Use lower_case_table_names=1 on all systems. Note Although database, table,...

I ran a query in mysql and it hangs for a while and then I get killed as a response. mysql> select * from usagehistory; Killed SHELL:~$ SHELL:~$ date Thu Mar 14 06:26:17 UTC 2019 SHELL:~$ Reason The mysql process/client itself runs out of memory and is terminated by the OS. I do not have a good view of the implementation, but it appears that mysql is trying something like pulling the whole table into memory to do the select *. You can use the --quick mysql option to not buffer the results in memory. I also found out that the data in table is around 2 GB and Memory in my jump-box is around 1 GB. Two more proofs that I see in my jump box (where mysql is run) system is: 1. I see OOM messages in syslog. root@ip-XXX-XXX-XXX-XXX:~# grep mysql /var/log/syslog | less Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301028] mysql invoked oom-killer: gfp_mask=0x24201ca, order=0, oom_score_adj=0 Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301032] mysql cpuset=/ mems...

I faced a strange situation, so I thought to share it with you, in case someone else finds himself in similar situation. I added a column to an existing table in MySQL. I also added a foreign key constraint on that column. So what is strange about that. Let me show you. First of all let me display the original structure of the table. mysql> SHOW CREATE TABLE T_DEVICE_RESULTANTSTATE\G *************************** 1. row *************************** Table: T_DEVICE_RESULTANTSTATE Create Table: CREATE TABLE `t_device_resultantstate` ( `RESULTANTSTATEID` int(11) NOT NULL AUTO_INCREMENT, `RESULTANTSTATE` blob, `DEVICEID` int(11) NOT NULL, `CREATEDBY` varchar(50) NOT NULL DEFAULT 'ADMIN', `CREATEDATE` datetime DEFAULT NULL, `MODIFIEDBY` varchar(50) NOT NULL DEFAULT 'ADMIN', `MODIFIEDDATE` datetime DEFAULT NULL, PRIMARY KEY (`RESULTANTSTATEID`), KEY `FK_T_DEVICE_RESULTANT_STATE_T_DEVICE` (`DEVICEID`), CONSTRAINT `FK_T_DEVICE_RESULTANT_STAT...

I was working on a customer case where I ran into an interesting problem - Query joining about 23 tables got stuck in statistics stage. The customer need to generate a report in pipe separated values format from their database. Since the database is hosted as AWS MySQL RDS, using SELECT ... INTO OUTFILE was not an option. Therefore I decided to dump the data by using mysql command line tool along with sed command to convert tab delimited to pipe delimited column values. mysql -e "select te.EId AS \"EID\", tea.AId AS \"AID\", tea.ActivationState AS \"Status\", tp.PRDName AS \"Product\", tea.FamilyId AS \"Product Family\", tp.Ver AS \"Product Version\", tc.CSTMRName AS \"Customer Name\", tc.CSTMRIdentifier AS \"Customer ID\", tea.ActivationDateTime AS \"Activation Date\", tea.quantity AS \"Activated Quantity\", caswuid.AttrValue AS \"SWUID\", cadm.AttrValue AS \"D...

What is a mutex? In computer programming, a mutex (mutual exclusion object) is a program object that is created so that multiple threads in the application can take turns sharing the same resource, such as access to a file. Typically, when a program is started, it creates a mutex for a given resource at the beginning by requesting it from the system and the system returns a unique name or ID for it. After that, any thread needing the resource must use the mutex to lock the resource from other threads while it is using the resource. If the mutex is already locked, a thread needing the resource is typically queued by the system and then given control when the mutex becomes unlocked (the thread that will get the mutex depends upon the thread scheduling algorithm applicable to the mutex context in question). This is needed to avoid race condition and to employ synchronization among threads to keep the database system consistent all the time. What is a Race Condition? A race conditio...

MySQL tables, including MyISAM and InnoDB, two of the most common types, can experience fragmentation. In order to understand fragmentation, let us consider How MySQL stores data for a table on disk in order to make it persistent. The data is stored on the disk in the form of files that are divided into fixed size extents, pages or blocks. The number of files that will be created to store data related to a single table, depends on the table's storage engine as well as the setting of related system variables. Hence one part of table's fragmentation is dependent on its storage engine, if it stores data in a table by using the techniques as shown below, that leads to fragmentation. Another part is external to MySQL and is platform/OS dependent. Table fragmentation means gaps in the information stored in the table, which comes into picture due to the following reasons: Logical fragmentation Some index types stores the record in a particular order of  their index key colu...

The MySQL server spawns a separate background thread for each enabled protocol that it supports on the target platform. This single thread per protocol further manages each client connection and helps to deliver a separate thread for each connection request. There can be different supported protocol depending upon the platform like Unix socket on Unix like platform, Named Pipe and Shared Memory on Windows platform. The MySQL server listens for TCP/IP connections (remote connections) from the client on all platforms. The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. To specify an address, use the --bind-address=addr option at server startup, where addr is an IPv4 or IPv6 address or a host name. If addr is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, th...