MySQL - Select date with microseconds precision

SELECT DATE_FORMAT( CURRENT_TIMESTAMP, "%W %M %e %Y %f");
SELECT now(6);
SELECT MICROSECOND(now(6));

Python - Replace three consecutive dot-space strings (. . . ) with triple dot (...)

import re
str = "This is one  . . .  . . This is two. And other."
print re.sub( r'((\. )\2{2,2})', '...', str)

MacOS - How to verify a file's sha256 checksum

Terminal command:
openssl dgst -sha256 /PATH/FILE_NAME

Yields:
SHA256(/PATH/FILE_NAME)= 77aa9930be611ec3f4e72d74c897dac5a98b4e61884a4048d31e4597f9fbb9ff

MySQL - GROUP_CONCAT Character Limit Setting

Run this session variable before your SQL.
Adjust the number of characters.

SET SESSION group_concat_max_len = 5000;

MySQL - Table is "Read Only" error message

(1) Locate MySQL's data folder path (run this query, MySQL must be up and running).
select @@datadir;

(2) Change permissions so "mysql" can access it:
chown -Rf mysql:mysql /PATH_TO_DATA_FOLDER/data

(3) Restart MySQL.

(4) Re-attempt to make table/record changes.

Collibra - DGC Workflow to export a domain view and save it as an attachment in that domain

import com.collibra.dgc.core.file.FileInfo;

// Define the view to download
// Get this from the "excel" object produced behind the scenes when you manually export the view (use Firefox or Chrome's inspector... Network Resources tab)
def tableViewConfig = '{"TableViewConfig":{"displayLength":-1,... "fieldName":"Asfdb5d2c95585d5af41aed"}}]}}'

// File Name
def fileName = "Your file name here.xlsx"
execution.setVariable( "fileName", fileName )
loggerComponent.info("File Name:" + fileName )

// Produce the file
def fileInfo = outputViewComponent.exportExcelWithoutJob( tableViewConfig, fileName, null, true, true);

// Get the file's Id
def fileId = fileInfo.getUuid()
execution.setVariable( "fileId", fileId )
loggerComponent.info("File Id:" + fileId )

// Define the target URL
outputUrl = applicationComponent.getBaseURL() + "rest/1.0/file/" + fileId
loggerComponent.info( "Output URL: " + outputUrl )

// Add as an attachment to the hosting/calling domain
// This task may need to reside within a separate script task, for synchronous execution
attachmentComponent.addAttachmentToResource( item.id, item.type.name(), fileId, fileName )

Collibra - Workaround for groovy string size limitation in Collibra DGC workflows

def VARIABLE_NAME = new TextArea( “LONG_STRING_HERE” )

PS. Tentative, yet to confirm. I assume then you can pass this variable from one workflow to another.

Meteor - Production deployment ignores CSS/Stylus

This happens, amongst perhaps other causes, due to a combination of the existence, in your project, of two packages in your project:

(1) Stylus
(2) Standard CSS Minifier

If you are using Stylus, remove the Standard CSS Minifier... that worked for me.

MongoDB - Sub-document filtering to return only desired sub-documents

db.COLLECTION_NAME.find(

    // Filtering. match
    {
        "FIELD_TO_FILTER_BY": FILTER_VALUE
    }

    // Projection
    , {
        ARRAY_OF_SUBDOCS:{
            $elemMatch:{"SUB_DOC_FIELD": FILTER_VALUE }
        }
    }
);

RegEx - Repair broken lines of text (paragraphs with cut lines often due to pagination)

Search for: \n(^[a-z])+
Replace with (remove double quotes): " $1"


PS. Use a double "\n" if the paragraph is split apart by two lines... use three if by 3 and so on.

TOMCAT - Users setup (tomcat-users.xml), in order to gain access to server status and manager

<?xml version="1.0" encoding="UTF-8"?>
<tomcat-users xmlns="http://tomcat.apache.org/xml"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://tomcat.apache.org/xml tomcat-users.xsd"
              version="1.0">


  <role rolename="tomcat"/>
  <role rolename="manager"/>
  <role rolename="admin"/>
<role rolename="manager-gui"/>
  <role rolename="admin-gui"/>

<user username="admin" password="PASSWORD_HERE" roles="admin,manager,tomcat,manager-gui,admin-gui"/>
 
  <user username="tomcat" password="PASSWORD_HERE" roles="tomcat,admin,manager"/>


</tomcat-users>

TOMCAT - It won't start and the logs say %1 is not a valid Win32 app

Open C:\xampp\tomcat\bin\tomcat7w.exe
Select "Use default" under Java Virtual Machine.
Otherwise point to the one you want it to use.

Collibra - Successful log sample (after changing JRE memory limits)

This is what a successful setup of a Collibra windows sandbox looks like... DGC 4.6.1 on Tomcat 9, using Java JDK's JRE 1.8.0_131-b11 64-bit.

• Log file name: catalina.2017-07-14.log
• Log file rows prefix: [TIMESTAMP] INFO [main] org.apache.catalina.startup.VersionLoggerListener.log ...
• Details:
1. Server version:        Apache Tomcat/9.0.0.M22
2. Server built:          Jun 21 2017 09:44:18 UTC
3. Server number:         9.0.0.0
4. OS Name:               Windows 7
5. OS Version:            6.1
6. Architecture:          amd64
7. Java Home:             C:\Program Files\Java\jdk1.8.0_131\jre
8. JVM Version:           1.8.0_131-b11
9. JVM Vendor:            Oracle Corporation
10. CATALINA_BASE:         C:\tomcat
11. CATALINA_HOME:         C:\tomcat
12. Command line argument: -Dcatalina.home=C:\tomcat
13. Command line argument: -Dcatalina.base=C:\tomcat
14. Command line argument: -Djava.io.tmpdir=C:\tomcat\temp
15. Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
16. Command line argument: -Djava.util.logging.config.file=C:\tomcat\conf\logging.properties
17. Command line argument: exit
18. Command line argument: -Xms128m
19. Command line argument: -Xmx256m
20. Command line argument: -Xmx4096m
21. Command line argument: -Xms512m
22. Command line argument: -XX:MaxPermSize=300m

 The last three confirm the limits on the JRE were expanded.

TOMCAT - How to increase the Java memory/heap limit

Add an environmental variable, like this (Linux/MacOS):
export _JAVA_OPTIONS="-Xmx2048m -Xms512m -XX:MaxPermSize=300m"

On windows
Environment variable name: _JAVA_OPTIONS
Value: -Xmx2048m -Xms512m -XX:MaxPermSize=300m

Linux - List files matching a string pattern

find . -maxdepth 1 -name "*STRING_BEING_SOUGHT*" -print
 

JIRA - Remove or alter cloned sub-tasks prefix

JIRA Administration
> System
> Advanced Settings (top right button)
> jira.clone.prefix

EXCEL - Macro/sub to delete partially selected rows

Sub sDeleteRows()
    rngStart = Selection.Cells(1, 1).Address
    rngEnd = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Address
    firstRowNumber = getRow(rngStart)
    lastRowNumber = getRow(rngEnd)
    Rows(firstRowNumber & ":" & lastRowNumber).EntireRow.Delete
End Sub

EXCEL - Macro/sub to join text cells

Sub sJoinCells()

    temp = ""
    rngStart = Selection.Cells(1, 1).Address
    rngEnd = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Address
    firstRowNumber = getRow(rngStart)
    lastRowNumber = getRow(rngEnd)
    rowCount = lastRowNumber - firstRowNumber
    Debug.Print firstRowNumber & " to " & lastRowNumber & " (rowCount: " & rowCount & ")"

    ' Validation: exit if only one cell selected
    If rowCount < 1 Then
        Debug.Print "sJoinCells: Single cell selected, nothing to do."
        Exit Sub
    End If

    ' Accumulate the values
    For i = 1 To Selection.Rows.Count

        ' Cell value cleanup
        v = Trim(Selection.Rows(i).Value)

        ' If the cum value does not end with a period, then make sure
        ' the first letter of this cell is NOT capitalized
        If Left(temp, 1) <> "." Then
            v = lower(Left(v, 1)) & Right(v, Len(v) - 1)
        End If

        ' Accumulate
        temp = temp & v & " "
    Next i

    ' Set concatenated values on first row
    Debug.Print "temp: " & temp
    'Selection.Rows(1).Value = temp

    ' Delete rows
    'Debug.Print (firstRowNumber + 1) & ":" & lastRowNumber
    'Rows((firstRowNumber + 1) & ":" & lastRowNumber).EntireRow.Delete
 
End Sub

Python - How to split a string into paragraphs

data = 'paragraph number one\n number two'
p = 0
for l in data.split('\n'):
p+=1
if l != '':
print p, '\t', l

PS. Assuming paragraphs are delineated by a carriage return (\n).

MySQL - How to delete the MySQL Windows Service

Windows terminal command (MD-DOS prompt): 


sc delete MySQL

MySQL - How to stop the MySQL server from the command line

mysqld stop

MySQL - How to change root user's password

(1) Create a custom initialization file containing this (e.g. named custom-init.txt):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_NEW_PASSWORD';

(2) Start MySQL using this command: 

mysqld --init-file=C:\\mysql\custom-init.txt

PS. Variants of the alter user statement may be necessary for MySQL versions earlier than 5.7

Meteor - How to upgrade meteor

meteor update

MySQL - GROUP_CONCAT function to list one-to-many relationships as comma-separated values

SELECT
m.SectionId
, GROUP_CONCAT( d.TranslationId ) AS TranslationIds
FROM
  TABLE_1_HERE AS m
  INNER JOIN TABLE_2_HERE AS d
  ON m.SectionId = d.SectionId
GROUP BY
  m.SectionId
;

PS. In this example, a section can have multiple translations. The result will be that, for each section row, multiple translation values will be calculated inside a single field.

MySQL - How to copy/rename a database/schema

$ cd /PATH_TO_MYSQL/bin
$ mysqldump -u USER_NAME_HERE -p OLD_DB_NAME_HERE >~/DUMP_FILE_NAME_HERE.sql
$ mysql -u USER_NAME_HERE -p
mysql> create database NEW_DB_NAME_HERE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> use NEW_DB_NAME_HERE
mysql> source ~/DUMP_FILE_NAME_HERE.sql
mysql> drop database OLD_DB_NAME_HERE;

MySQL - Determine system folder location using a select statement query

select @@datadir;

MongoDB & MacOS - How to create a file that will start the server on double click

Create a simple text file with the command (below is the command I use), and name it with a ".command" extension.

Then gain execution rights on it.
chmod u+x /PATH/FILE_NAME.command

The command... customize the path to fit your system.
/Applications/mongodb/bin/mongod --dbpath /PATH_TO_DATABASE

RegEx - Find three-digit numbers

([0-9][0-9][0-9])+

This will also find the first three digits of, for example, number 9999.
PS. I am not convinced the last plus sign is absolutely required.

Regex - Remove brackets surrounding integer numbers

Search for "any text here: <([0-9]+)+>"
and replace it with "any other text here: $1"

This will replace "any text here: <342>" with "any other text here: 342"

Regex - Find numbers not next to character "<"

^\<([0-9])+

Of course, substitute "<" with any other desired character.

Excel - MacOS Path to Personal Macros Library

~/Library/Containers/com.microsoft.Excel/Data/Library/Application Support/User/PERSONAL.xlsm

To prevent it from opening every time you launch excel, open excel, focus on your personal macros workbook, then go to "Window > Hide".

MongoDB - How to count the number of sub-documents in a given field

db.collection.aggregate(
    { $unwind: '$field.with.subojects.here'}
    , { $group: {
        _id: '$_id'
        , _count:{ $sum: NumberInt(1) }
    }}
);

Meteor - MongoDB update using a computed/dynamic field name

v = "secondPart";
collectionName.update(
{ filterHere: "example" }
, {
$set: {
    [ 'firstPart.' + v + '.thirdPart' ]: "fieldValue"
}
}
);

MongoDB - How to rename a field within an array of objects/sub-documents?

db.collection_name.find({

// Filter to only work on documents that contain the array field
"parent_field.child_old_field_name": { $exists : 1 }

}).forEach(
    function( doc ){

    // For each object in the current document's array
        for( i=0; i < parent_field.length; ++i ) {

            // Create the new field
            doc.parent_field[i].child_new_field_name = doc.parent_field[i].child_old_field_name;

            // Delete the old field
            delete doc.parent_field[i].child_old_field_name;
        }

        // Update the document
        db.collection_name.update(
        // Only the current document
        { _id : doc._id }
        // The updated document (as per the statements in the above "for" loop)
        , doc 
        );
    }
);

// This is how the collection that fits the example looks like:

{

    "_id" : "bs2XTNDYLSrp9Ae9m"

    , "parent_field" : [ 

        {

            "child_old_field_name" : "any value" 

        }

        , {

            "child_old_field_name" : "Any other value"

        }        

    ]

}

MongoDB - How to make a case insensitive query/filter

db.collection_name.find({
    "field_name": /^search_term_here/i
});

PS. Make sure to use "^" in order for the query to use the index, if available, to avoid a full scan.

MongoDB - How to update a field using the value from another field

db.collection_name.find({filter_here_if_any}).forEach( function(doc) {
    doc.target_field_name = doc.source_field_name;
    db.collection_name.save( doc );
});

CSS - How to remove the blue halo a browser adds to text input fields and editable divs

outline: none;

CSS - Mixing fixed and percentage widths

<style>
  .comment {
    position: relative;
    padding-left: 100px;
  }
  .comment-left {
    position: absolute;
    top: 0;
    left: 0;
    width: 100px;
    border: 1px solid blue;
  }
  .comment-right {
    width: 100%;
    float: left;
    border: 1px solid red
  }
</style>


<div class="comment">

  <div class="comment-left">
    something
  </div>

  <div class="comment-right">
    something else
  </div>

</div>