MySQL set DB collation

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

package com.infonow.service.recovery.controller;

import java.util.Iterator;
import java.util.List;

import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.infonow.framework.logging.Level;
import com.infonow.framework.logging.Logger;
import com.infonow.framework.service.context.ServiceContext;
import com.infonow.service.recovery.AbstractRunner;
import com.infonow.service.recovery.TranslationOrphan;

import static org.apache.commons.collections4.CollectionUtils.collect;

public class TranslationController extends ServiceCallerBatchController<TranslationOrphan>
{
    private static final String NAME = "Translation";

    public TranslationController()
    {
        super(NAME);
    }

    @Override
    public List<TranslationOrphan> loadIds(int numToRead)
    {
        List<TranslationOrphan> relationOrphans = getRecoveryQueries().getTranslationOrphans(numToRead,"PENDING");//new attribute here named status, gets all the orphans which are in pending state
        //TranslationController will load orphans that are in PENDING(or NULL for backward compatibility) status to be processed by TranslationService, when does the entry in the table happen?
        return relationOrphans;
    }

    public void changeStatus()
    {
        List<TranslationOrphan> relationOrphans = loadIds(10);

    }

    @Override
    protected RecoveryRunner<List<TranslationOrphan>, ?> createRunner(List<TranslationOrphan> ids)
    {
        return new InowRelationRunner(ids);
    }

    @Override
    protected void cleanUp(RecoveryRunner<List<TranslationOrphan>, ?> runner)
    {
        List<TranslationOrphan> ids = runner.getId();//skip the orphans which are in pending or inprogress state
        //loop over ids, create a new list of ids without inprogress and pending status
        System.out.println("ids");
        System.out.println(ids);
        getRecoveryQueries().deleteTranslateOrphans(ids);
    }

    @Override
    public int getNumberOfIdsPerBatch()
    {
        return getNumberOfIdsToRead();
    }

    /**
     * The Runnable instance use to process a batch of staging Addresses
     */
    private class InowRelationRunner extends AbstractRunner<List<TranslationOrphan>, Object>
    {
        ServiceContext _context = null;

        private Logger LOG = Logger.getLogger(TranslationOrphan.class.getName());

        InowRelationRunner(List<TranslationOrphan> ids)
        {
            super(ids, NAME, getService(), getTransactionTemplate());
            _context = new ServiceContext();
            _context.setContextType("default");
        }

        @Override
        public Object loadDataObject(List<TranslationOrphan> ids)
        {
            return loadMatchType(ids);
        }

        @Override
        public Object loadUserObject(List<TranslationOrphan> ids)
        {
            // There is a bug in AbstractRunner where the userObject and userData are swapped.
            // I don't want to change it because it might break every service, so I'll just
            // flip the objects here like the other services seem to do.
            // This is actually the userData in the service.
            return ids;
        }

        @Override
        public String loadMatchType(List<TranslationOrphan> ids)
        {
            return null;
        }

        @Override
        public ServiceContext createServiceContext(Object dataObject)
        {
            return _context;
        }

        @Override
        protected void handleRunException(Exception exception)
        {
            // We are going to make a new orphan with an incremented retry count
            // and put it back in the queue.
            List<TranslationOrphan> oldOrphans = (List<TranslationOrphan>) this.getId();

            for (Iterator<TranslationOrphan> i = oldOrphans.iterator(); i.hasNext();)
            {
                TranslationOrphan oldOrphan = i.next();

                long newRetryCount = oldOrphan.getRetryCount() + 1;

                // First, compute the severity of the exception to be logged based on the retryCount.
                Level loggingLevel = Level.WARNING;

                // We only make it SEVERE if the retryCount is a multiple of 10
                // to avoid hammering engineering support with e-mails.
                if ((newRetryCount % 10) == 0)
                {
                    loggingLevel = Level.SEVERE;
                }

                LOG.log(loggingLevel,
                        getDescription() + ":  Error processing id - " + getId().toString() + ", re-submitting orphan.",
                        exception);

                PlatformTransactionManager txnMgr = null;
                TransactionStatus status = null;

                try
                {
                    txnMgr = _transactionTemplate.getTransactionManager();
                    status = txnMgr.getTransaction(
                            new DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRES_NEW));

                    double delayMinutesAsDouble = 0.0;
                    int delayMinutesAsInteger = 0;

                    // Compute the delay minutes for the new orphan.
                    // Since there is the potential for numeric overflows,
                    // we are wrapping the math in a try/catch block.
                    try
                    {
                        delayMinutesAsDouble = Math.pow(2, newRetryCount);
                        delayMinutesAsInteger = (int) delayMinutesAsDouble;
                    }
                    catch (Exception e)
                    {
                        delayMinutesAsInteger = 7 * 24 * 60; // Push re-try out to 7 days if numeric overflows occur.
                    }

                    getOrchestrationDao().insertTranslationOrphan(delayMinutesAsInteger, oldOrphan.getInowProfileSid(),
                            newRetryCount);

                    try
                    {
                        if (!status.isRollbackOnly())
                        {
                            txnMgr.commit(status);
                        }
                    }
                    catch (Exception e)
                    {
                        String msg = getDescription() + ":  Error processing id - " + getId().toString();
                        LOG.log(Level.SEVERE, msg, e);
                    }
                }
                catch (Exception e)
                {
                    String msg = getDescription() + ":  Error processing id - " + getId().toString();
                    LOG.log(Level.SEVERE, msg, e);
                }
                finally
                {
                    try
                    {
                        if (txnMgr != null && status != null && !status.isCompleted())
                        {
                            txnMgr.rollback(status);
                        }
                    }
                    catch (Exception ee)
                    {
                        String msg = getDescription() + ":  Error processing id - " + getId().toString();
                        LOG.log(Level.SEVERE, msg, ee);
                    }

                }
            }
        }

    } // end inner-class InowRelationRunner

}

[root@jiaoyou mysql]# pwd
/var/lib/mysql
[root@jiaoyou mysql]# ls -ls
338256 -rw-rw---- 1 mysql mysql 346030080 2010-04-22 08:08 ibdata1
626812 -rw-rw---- 1 mysql mysql 641222072 2010-01-26 07:17 mysql-bin.000008
316892 -rw-rw---- 1 mysql mysql 324173772 2010-03-25 12:51 mysql-bin.000009
52724 -rw-rw---- 1 mysql mysql  53931666 2010-04-12 12:13 mysql-bin.000010
10136 -rw-rw---- 1 mysql mysql  10359639 2010-04-22 08:32 mysql-bin.000011

mysql> SHOW BINARY LOGS; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 | 641222072 | 
| mysql-bin.000009 | 324173772 | 
| mysql-bin.000010 |  53931666 | 
| mysql-bin.000011 |  10360680 | 
+------------------+-----------+


Solution

PURGE BINARY LOGS TO 'mysql-bin.010';

sudo killall mysqld
sudo /Applications/XAMPP/xamppfiles/bin/mysql.server start

ALTER DATABASE database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT id, account_id, amount, created_at, null as type
 	FROM subscription_invoice
UNION ALL
SELECT id, account_id, amount, created_at, 
	`type` as type
FROM subscription_payment
ORDER BY created_at DESC

Db::getInstance()->executeS("DELETE FROM product_images WHERE id_product NOT IN (" . join($allProductIds,',') . ")")

CREATE TABLE firends (
	id INTEGER,
  	firstname VARCHAR(64)
);

CURRENT_TIMESTAMP	

CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

SELECT ticker,`datetime`
FROM (
  SELECT ticker, MAX(`datetime`) `datetime`
  FROM Bloomberg_hist_data
  GROUP BY ticker
) t ORDER BY `datetime`

UPDATE deltadb.transactions_temp
SET ticker =(CASE CEIL(RAND()*2)
              WHEN 1 THEN '3CN:SP'
              WHEN 2 THEN 'AGV:SP'
          END);

CONCAT(',', section_id, ',') REGEXP ',(val1|val2|val3),'"