How to convert a Postgresql ltree structure to a nested set by using VIEW? I would like to ask a question about a way for transforming a PostgreSQL ltree structure to a nested set structure with only one query by using only VIEWs.

For example, I have a table which has data with relation to each other as on the picture below:

So, the table daclaration is

KeywordLtree(id INT PRIMARY KEY, value TEXT, path ltree);

-- And the data is:

pk |  value  |  path  |
0  |   'A'   |   ''   |
0  |   'B'   |   '1'  |
0  |   'C'   |   '2'  |
0  |   'D'   |  '1.3' |
0  |   'E'   |  '1.4' |
0  |   'F'   |  '1.5' |
0  |   'G'   |  '2.6' |
0  |   'H'   |  '2.7' |

And I have to convert this table to such table:

KeywordSets(id INT PRIMARY KEY, value TEXT, lft INT, rgt INT);

where the rules for the left and the right borders are done according to a nested sets rules.
I found the way how to obtain vertices on each level

CREATE OR REPLACE RECURSIVE VIEW bfs (id, value, path, num_on_level, level) AS
    SELECT id, value, path, row_number() OVER (), 0 as level
    FROM KeywordLtreeSandbox WHERE path ~ '*{0}'

    UNION ALL

    SELECT C.id, C.value, C.path, row_number() OVER (PARTITION BY P.path), level + 1
    FROM KeywordLtreeSandbox C JOIN bfs P ON P.path @> C.path
    WHERE nlevel(C.path) = level + 1;

-- Data would be like below
id | value | path | num_on_level | level |
0  |  "A"  |  ""  |      1       |   0   |
1  |  "B"  | "1"  |      1       |   1   |
2  |  "C"  | "2"  |      2       |   1   |
3  |  "D"  |"1.3" |      1       |   2   |
4  |  "E"  |"1.4" |      2       |   2   |
5  |  "F"  |"1.5" |      3       |   2   |
6  |  "G"  |"2.6" |      1       |   2   |
7  |  "H"  |"2.7" |      2       |   2   |

But I have no idea how to enumerate them correctly then (So "A" left = 1, right = 16, "B" left = 2, right = 9 and so on...)

If I need to be more clear please let me know.

Could anyone give me an idea how it is possible to do?

I think there is a way around this without using a recursive query:

WITH cte AS
  ( SELECT
        m.*,
        ( SELECT COUNT(*)
          FROM KeywordLtree AS d
          WHERE m.path @> d.path
        ) AS descendants,
        ( SELECT COUNT(*)
          FROM KeywordLtree AS a
          WHERE a.path @> m.path
        ) AS ancestors,
        ROW_NUMBER() OVER (ORDER BY m.path) AS rn
    FROM KeywordLtree AS m
  )
SELECT cte.*,
       2 * rn - ancestors AS lft,
       2 * rn - ancestors + 2 * descendants - 1 AS rgt
FROM cte
ORDER BY path ;


How to generate the E-R Diagram of Wordpress using mySQL Workbench I am trying to generate the E-R Diagram of Wordpress using the 'Reverse Engineer' feature. There is a slight obstacle though.
The diagram does not show the relationships between the entities. Is this the way it's supposed to work or am I missing something?

The EER diagram is correct. There should be no relationships because WordPress does not have any constraints. The EER diagram shown in the codex is only for reference (it's a dummy EER diagram). You must rely on other logical operations to make sure you have no orphaned data.

Source: https://codex.wordpress.org/Database_Description

  Please note that within the standard installation of WordPress no
  integrity between the tables is enforced e.g. between posts and
  comments. If you are creating a plugin or extension that manipulates
  the WordPress database, your code should do the housekeeping so that
  no orphan records remain in the tables e.g. by removing records in
  other tables with a set of SQL commands when foreign keys are deleted
  (Don't forget to remind users to backup before such operations).


How to migrate from a MySQL master-slave setup to a master-master setup without downtime We have an existing master-slave MySQL replication setup where we would like to promote the second slave to a master in order to achieve a master-master setup with no or only minimal downtime.

Is it sufficient to do a SHOW MASTER STATUS on the slave and do a CHANGE MASTER TO ... on the current master or will this result in a replication error?

On a basic level, yes. Just set up the current Master like you would any normal slave database.

However, there are some things to consider first.

Is the second slave set to READ-ONLY, as you want to avoid writing to both masters at once.

You probably want to set auto_increment_increment=2 and auto_increment_offset=1 on the first master, and auto_increment_increment=2 and auto_increment_offset=2 on the second master.

This prevents the two masters from creating the same auto_increment primary keys.

You also want to make sure you have no replication filters (binlog-do-db, replicate-do-db etc) set up as they can cause problems too).


Data Warehouse: Staging Area, DWH databases and ETL tool on one server? I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the transformation from stg to dwh running on one single machine because of reduced hardware cost and less administration effort:

sda: 512 GB SDD -> OS (Centos 7), Applications, Swap space, MySQL
Database application (but no data), csv files from data sources, ETL
tool (Pentaho)
sdb: 960 GB SDD -> MySQL tablespace for staging area tables
sdc: 960 GB SDD -> MySQL tablespace for data warehouse tables
sdd: 4 TB HDD -> backup & archive disk
Memory: 160 GB
Prozessors: 2x Intel Xeon E5-2620v3 - 12 cores

Tasks:

During stg loading: LOAD DATA INFILE from csv to stg
During transformation: Pentaho would SELECT from stg and INSERT into dwh in parallel for multiple tables. I thought having stg and dwh on separate disks will avoid read / write bottlenecks
During working hours: Reporting tool or Analysts do SELECT on dwh

From your point of view or experience is there any reason why I should not use this layout and instead split stg and dwh on two different servers like I did in the past? (e.g. MySQL performance might suffer from parallel SELECT and INSERT, etc.)

Thanks for your feedback!

Your "all your eggs in one basket" scenario seems ripe for unintended consequences. I try to decouple processes so that something that runs amok doesn't propagate a negative effect to other parts of your overall DW architecture. Using separate disks is definitely good practice and even splitting , bear in mind that memory and CPU constraints are just as important points of contention.

Here's a scenario that I've seen in designs like yours ----

A sql plan changes (due to data quantity or quality changes) and you come back in the next morning and realize the ETL's still going and the additional CPU and memory draw is affecting query performance. This puts you in the unsavory situation wherein you're wondering whether it's far enough along to finish the load or you should terminate it and do a catch up load later (which may have even more data) to let users get at their data.

Decoupling some of your architecture gives you the freedom to get your staged data a second look to review issues, run validation checks, and most importantly give you the flexibility of troubleshooting during the work day without worrying about sharing resources.

In any case, if you do decide to run via the single box route (which I'd highly recommend against), consider:

Making sure the right tables use the right engine for the appropriate requirement
CPU limiting certain processes so that they don't starve the server
Ensuring alerts and notifications are properly set up to allow you to keep track of errors, completions, and long running processes
Test, test, and.... TEST. test with different data sizes. test with a variety of data. and test by simulating issues like memory pressure, etc.

It should be self-evident that what I've said is a conjunction of a few best practice recommendations and my personal experience. You should account for your business requirements and its capacity for risk. i.e.: In some industries, having a data warehouse loaded with the previous day's data is a highly critical requirement. In others, just having access to the data warehouse is the most critical requirement.


Can this array group count query be improved? So I have the following query

explain analyze
with tags as (
    select unnest(tags) as tag_name from tasks where user_id = 1
) select
        count(9),
        tag_name
    from
        tags
    group by
        tag_name
    order by
        count(9) desc
    limit 50

Gives me the following result:

Limit  (cost=3243.86..3243.99 rows=50 width=32) (actual time=2.278..2.278 rows=1 loops=1)
  CTE tags
    ->  Bitmap Heap Scan on tasks  (cost=12.35..1917.72 rows=52700 width=13) (actual time=0.098..2.074 rows=261 loops=1)
          Recheck Cond: (user_id = 1)
          ->  Bitmap Index Scan on index_tasks_user_id  (cost=0.00..12.22 rows=527 width=0) (actual time=0.065..0.065 rows=261 loops=1)
                Index Cond: (user_id = 1)
  ->  Sort  (cost=1326.14..1326.64 rows=200 width=32) (actual time=2.278..2.278 rows=1 loops=1)
        Sort Key: (count(9))
        Sort Method: quicksort  Memory: 25kB
        ->  HashAggregate  (cost=1317.50..1319.50 rows=200 width=32) (actual time=2.273..2.274 rows=1 loops=1)
              ->  CTE Scan on tags  (cost=0.00..1054.00 rows=52700 width=32) (actual time=0.099..2.177 rows=261 loops=1)
Total runtime: 2.314 ms

Which is pretty decent I suppose. The previous way of doing things where to have a bunch of join tables and that gave me something like below:

Limit  (cost=919.38..919.40 rows=50 width=12) (actual time=163.164..163.257 rows=50 loops=1)
  ->  Sort  (cost=919.38..919.48 rows=206 width=12) (actual time=163.162..163.194 rows=50 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 28kB
        ->  HashAggregate  (cost=917.39..918.01 rows=206 width=12) (actual time=162.899..163.008 rows=132 loops=1)
              ->  Nested Loop  (cost=456.90..917.19 rows=206 width=12) (actual time=1.040..162.361 rows=416 loops=1)
                    ->  Hash Join  (cost=456.90..904.32 rows=206 width=4) (actual time=1.029..159.429 rows=416 loops=1)
                          Hash Cond: (taggings.workout_id = workouts.id)
                          ->  Seq Scan on taggings  (cost=0.00..416.64 rows=40214 width=8) (actual time=0.010..45.753 rows=37029 loops=1)
                          ->  Hash  (cost=455.91..455.91 rows=282 width=4) (actual time=1.004..1.004 rows=293 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                ->  Bitmap Heap Scan on workouts  (cost=4.49..455.91 rows=282 width=4) (actual time=0.101..0.744 rows=293 loops=1)
                                      Recheck Cond: (user_id = 1)
                                      ->  Bitmap Index Scan on index_workouts_on_user_id  (cost=0.00..4.48 rows=282 width=0) (actual time=0.058..0.058 rows=293 loops=1)
                                            Index Cond: (user_id = 1)
                    ->  Index Scan using tags_pkey on tags  (cost=0.00..0.06 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=416)
                          Index Cond: (id = taggings.tag_id)
Total runtime: 163.393 ms

Now forget about the last explain and lets focus on the first one. Can it be optimized further? Any tricks or such that I might be missing out on? I guess an index on the user_id column should be plenty for this query?

I doubt that your query can be further optimized for performance.  Right now it is making good use of indexes so there is not much more to be done.  The only example that might be worth trying might be to move the CTE into a view but I doubt that will have much of an effect either way (it may be worth trying however, because CTE scans are somewhat special, and they are separately planned, which makes no change in your favor here).

One thing I would point out is that there are a few things that can be done for readability.  In PostgreSQL, the convention is to use "count(*)" which the planner knows to mean to just count the number of rows returned.  This does not use up more memory than count(1) or count(9) and it is a lot more readable.   With an expression like count(9), I encounter it so seldom, I actually have to stop and think about what it means, which is not a good thing when it comes to queries.


Storing not very volatile information in database with cache or mongodb For a new project I'm working on right now I was doubting on which database/storage system to use for specific information which does not change that often.

We have for instance some company-profiles which will have only incidental writes to it when things like opening-hours change. On the other hand they have a lot of read-throughput; at every page load the information has to be loaded. Storing it at the filesystem as plain documents results in very much IO; Using that as a starting point I was searching for some sort of in-memory-solution.

Normally I store this information in a Postgres database, without any caching in the application-layer. I could off course add a caching layer in redis or memcached to produce lesser IO reads from disk, and handle updates of the cache at the application-layer on update of the information

An other solution is to use MongoDB to store the 'documents'. But I don't know how mongodb handles the read/writes. Eg with every retrieval of the document does it check the filesystem, or does it serve the document from cache/RAM? (I read somewhere mongodb keeps a lot of RAM available for itself)

Can someone shed some light on this?

The answer as always is: it depends ;)

With a bit of simplification: Indexes and recently read (and written) documents are kept in RAM until that RAM is needed for something else. So wether your data is read from RAM or from file pretty much depends wether RAM on the MongoDb node(s) is sufficient to keep the index and at least part of your data (called the working set) in RAM. It is safe to say that the only thing that can replace having sufficient RAM in a MongoDB installation is to have more RAM.

So the question is: how much is sufficient RAM? And the answer again is: it depends. On the size of your database, the number of documents in it's collections, the number of indices you need. It is by no means easy to figure out the right values and that is very dependent on the situation, but I will try to give you some rules of thumb.

Whenever possible, use SSDs. Rather take half the amount of RAM and SSDs than double the amount of RAM and HDDs.
Generate a reasonable fraction of your expected data size (or use a reasonable fraction of it if you already have data). The values can be completely random, though their size should be somewhat realistic (you rarely have company names 255 characters long, for example)
Create the indices as you expect to need them. Rule of thumb: generate an index for every field or combination of fields you expect to query for. Keep in mind that a field in an compound index will be used if you query for that field alone. You really need to put some effort in this step.
Sum up the index sizes.
If your combined index size for the test data exceeds your physical RAM, put some more into the machine, at least 1.5 times the value of your combined index sizes.
Run your performance tests.

While this sounds a bit complicated, it may save you from developing (and maintaining!) a whole layer in your application. This easily sums up over time.

Note for general scaling: Calculate the index sizes for your expected data set. Multiply that value by 1.5 at least. I'd rather suggest multiplying by >3. Take this as your memory size. The factor is actually determined by your growth factor and limited by the sweet spot where your hardware delivers the most bang for the buck. If your RAM requirements are exceeding the RAM of your hardware sweet spot, you should shard your cluster right away.


Select query to retrieve rows with null values I need to retrieve data from a table even if one of the fields has null value. Here's an example:

Select name, SUM(credit) as credit
From expenses
Where name like 'vendor0%'
and date like '2013%'
Group by name
Order by name asc

This example retrieves name and SUM(credit) only when credit has values. I need to retrieve all from name even if credit has no value at all.

Is this possible?

This example retrieves only "name" and the "SUM(credit)", when the
  "credit" has values.

The query you presented will retrieve a row for every present name, even if all associated credit columns are NULL. You get a row with a NULL value for SUM(credit) then. Null values are just ignored by the aggregate function sum():

You only get no row for a particular name if no row for that name exists in the table expenses for the given WHERE expressions.

I am assuming you want
.. only names matching 'vendor0%'
.. but all of those, even if they have no expenses in 2013.

Your query could work like this:

SELECT name, SUM(CASE WHEN date LIKE '2013%' THEN credit END) AS credit
FROM   expenses
WHERE  name LIKE 'vendor0%'
GROUP  BY name
ORDER  BY name

CASE defaults to NULL if no ELSE branch is given.
Aside: You shouldn't store date / time values as text. Use an appropriate type, it has many advantages.
And don't use "name" or "date" as identifiers. "name" is not a descriptive name and "date" is a reserved word in standard SQL and a function and base type name in Postgres.


Comfortable sqlplus interface? I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available.

What is a good replacement / extension for sqlplus? Could be a GUI or better (so it stays useful via SSH) a command line utility.

SQL*Plus is the main command line tool to operate with the Oracle Database.

but for example no "arrow-up" key for the previous history entry is available.

You're talking about sqlplusw the "GUI" tool - the oldschool sqlplus does support
arrow-up-bring-previous-statements feature.

  What is a good replacement / extension for sqlplus

Oracle's SQL Developer, a free GUI-based tool, is pretty neat.

If you're a vim addict, there's VoraX which is augments SQL*Plus very well.

  VoraX aims to be a simple, yet a powerful IDE for Oracle databases. Instead of reinventing the wheel, VoraX relies on the well-known SqlPLUS which is actually used to connect to the database, to execute SQL statements and to get the results back. However, this interaction is hidden from the user who just uses his/her Vim environment without knowing that, behind, a SqlPLUS process does all the work.

  The big advantage of this approach is that almost all SqlPLUS commands may be used without a problem. You can format columns, set pagination, autotrace queries etc.

  FEATURES

  99.9% of the sqlplus commands supported
  advanced code completion
  it runs on Windows and UNIX
  a nice results window with highlighting for errors and logging support
  a database explorer which may be used to browse the database objects in a hierarchical way
  support for editing and compiling PLSQL objects like packages, types etc.
  convenient key mappings for: describe object under cursor, execute current statement etc.
  connection profiles management (including securely storing of passwords)
  fuzzy search for database items
  offline search for Oracle html documentation


1286 - Unknown storage engine 'InnoDB' I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a table:

1286 - Unknown storage engine 'InnoDB'

and

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

and

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Ideas as to how to fix? It used to work just fine.

OBSERVATIONS

I see there is nothing Innodb-related in the my.cnf.
Look for Innodb-related error messages expressing why mysqld failed to start in the error log.

My first guess would be look at the ib_logfile0 and ib_logfile1 in /var/lib/mysql.

If they have been set to size different from the default, that probably will prevent mysqld's startup. The default for innodb_log_file_size is 5M.

SUGGESTION #1

If RoundCube's installation overwrote the my.cnf, see if you have a physical backup of the previous my.cnf. Put that back in place and run

service mysql start

SUGGESTION #2

Take the filesize of ib_logfile0 and divide by 1048576. Let's say it is 12M.

Put that in my.cnf

[mysqld]
innodb_log_file_size = 12M

then run

service mysql start

Give it a Try !!!


Do I need an auto-generated ID in addition to a unique field? I have a table that represents a list of currencies. Here is a simplified version of it:

CREATE TABLE currencies (
  id          UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  code        varchar(3) NOT NULL UNIQUE,
  name        varchar(255) NOT NULL,
  precision   integer NOT NULL
);

As you can see, I have an auto-generated id field of type UUID, but then I also have a UNIQUE code field. Are there any benefits of keeping both id and code fields? I have an idea to drop code and use id as holder for code values, since I need uniqueness for all the currency codes anyways. So the new table would like this

CREATE TABLE currencies (
  id          varchar(3) NOT NULL PRIMARY KEY,
  name        varchar(255) NOT NULL,
  precision   integer NOT NULL
);

Seeing as your question appears to hinge on preference, any answer seems necessarily subjective. Given Akina's point that some systems require UUID, it might be useful to have such a field in place if you foresee such an eventuality.

If you're at all concerned with the cumulative effects of your choice on your storage needs, then you'd likely want to stick with your varchar(3) value, seeing as it will consume around 4 bytes, whereas a UUID value will clock in closer to 128 bits (16 bytes).

That being said, I don't imagine you would need to worry about that unless you're working with exceptionally large amounts of data, which appears unlikely seeing as you're limiting the length of the varchar id to three characters, which caps your id column at 50,653 entries, provided you stick to alphanumeric values which use the English alphabet.

Beyond all that, you may want to use only one unique identifier so as to avoid any possible confusion from having two columns that essentially serve the same purpose.


mysql no longer defaulting to /tmp/mysql.sock I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock.

I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock restarted and mysql was on the new socket just fine.

Fixed my software.

Now I stopped mysql, removed socket statement from /etc/my.cnf, restarted mysql and it is still using /var/lib/mysql/mysql.sock.

I have restarted and cannot get it to use /tmp/mysql.sock unless I add it to /etc/my.cnf and I do not want to do that because of testing constraints.

Why would it no longer go to /tmp/mysql.sock when there is no configure parameter and there is no commmand line parameter (that I can see from ps aux) either?

Linux CentOS 6.5 Final
mysqld  Ver 5.6.17 for Linux on i686 (MySQL Community Server (GPL))

Help me with this please.

Thanx

Log entries

140624 10:37:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-06-24 10:37:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defa
2014-06-24 10:37:37 1277 [Note] Plugin 'FEDERATED' is disabled.
2014-06-24 10:37:37 1277 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2014-06-24 10:37:37 1277 [Note] InnoDB: The InnoDB memory heap is disabled
2014-06-24 10:37:37 1277 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2014-06-24 10:37:37 1277 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-06-24 10:37:37 1277 [Note] InnoDB: Using Linux native AIO
2014-06-24 10:37:37 1277 [Note] InnoDB: Not using CPU crc32 instructions
2014-06-24 10:37:37 1277 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-06-24 10:37:37 1277 [Note] InnoDB: Completed initialization of buffer pool
2014-06-24 10:37:37 1277 [Note] InnoDB: Highest supported file format is Barracuda.
2014-06-24 10:37:38 1277 [Note] InnoDB: 128 rollback segment(s) are active.
2014-06-24 10:37:38 1277 [Note] InnoDB: Waiting for purge to start
2014-06-24 10:37:38 1277 [Note] InnoDB: 5.6.17 started; log sequence number 6066054
2014-06-24 10:37:38 1277 [Note] Server hostname (bind-address): '*'; port: 3306
2014-06-24 10:37:38 1277 [Note] IPv6 is available.
2014-06-24 10:37:38 1277 [Note]   - '::' resolves to '::';
2014-06-24 10:37:38 1277 [Note] Server socket created on IP: '::'.
2014-06-24 10:37:38 1277 [Note] Event Scheduler: Loaded 0 events
2014-06-24 10:37:38 1277 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

The MySQL documentation states that the default path to mysql.sock is /var/lib/mysql/ on RPM distributions. The path is set at compile-time depending on the target OS and can be overridden by configuration or command line, so for CentOS the behavior you are seeing is correct.
This means that the socket path must have originally been set to /tmp by some other mechanism. There are several places this could have been done; likely in the init script /etc/init.d/mysql.server (to set socket=/tmp/mysql.sock on the command line - this can be done in an init script even though it is unsupported) or a local options file. See this page in the documentation for MySQL's precedence order when processing startup options. Entries listed later in the table will override the preceding ones.
It probably isn't possible to trace the sequence of events leading up to the socket path change, but fortunately you can change it via ~/.my.cnf, the user-specific option file.


Converting a VARCHAR to VARBINARY I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising.

However, it's come to the point where the query plans are taking up too much space (as we're storing the entire plan against each query).

I'm therefore attempting to normalise the existing data by extracting the QueryPlanHash and QueryPlan to another table.

CREATE TABLE QueryPlans
(
    QueryPlanHash VARBINARY(25),
    QueryPlan XML,
    CONSTRAINT PK_QueryPlans PRIMARY KEY
    (
      QueryPlanHash
    )
);

As the definition of the query_plan_hash in sys.dm_exec_query_stats is a binary field (and I'll regularly be inserting new data), I was using VARBINARY for the data type in my new table.

However, the insert below fails...

INSERT INTO QueryPlans
    ( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
    SELECT
      p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
      QueryPlan,
      ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
    FROM table
    CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1

....with the error

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

The problem is that the query plan hashes are already in binary format, however stored as VARCHAR in the XML Query Plan e.g.

0x9473FBCCBC01AFE

and CONVERT to BINARY gives a completely different value

0x3078393437334642434342433031414645

I tried changing the value definition in the XQuery select to binary, but then it returned no values.

How would I extract the value of 0x9473FBCCBC01AFE from an XML query plan as a VARBINARY, rather than a VARCHAR?

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'.

That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E:

SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);
------------ the ,1 is important ---------------^^^

Result is binary:

----------------
0x9473FBCCBC01AF


query to find the count of each member in a column Imagine there is a table with these columns

Table 1 : id
          Name
          Family

We want to find the count of each member in the column .
I wrote the query with CTE but I want to know if there are better solutions
in the picture below you can see the query and the result.

One way is to use COUNT() with OVER :

declare @vt_source as table
( id int not null,
  name varchar(50) not null)

 insert into @vt_source(id,name)
 values (1,'pantea')
 ,(1,'pantea')
 ,(2,'shidea')

 select distinct
    id
    ,name
    ,count(*)over(partition by id
                       order by name asc
                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as idCount
    ,count(*)over(partition by name
                       order by id asc
                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as nameCount
 from
   @vt_Source

output:

id  name    idCount nameCount
1   pantea  2       2
2   shidea  1       1

EDIT:
as @Lennart sugested, the count part can be rewritten like this:

,count(*)over(partition by id) as idCount
,count(*)over(partition by name) as nameCount


SQL Server - Recovery Pending Error We are running a series of Delete Queries to free up space on our SQL Server (2012)  I was able to recover files after the "Recovery Error", but it is very time consuming.

So I keep the size of my deletes to about 1 million records, and this seemed to prevent triggering the "Recovery Pending".

Is there a better approach?

Thanks much

Use a loop to delete and make frequent transaction log backups while doing so. That way people can use the table and you will keep the transaction log at a responsible size

DECLARE @ROWCOUNT int = 1
  WHILE @ROWCOUNT > 0
BEGIN
 delete  top (10000)
  from [SomeTable]
  where [Something]
 set @rowcount = @@rowcount;
END;


MySQL - How to see the queries executing by slave io thread We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using mysqlbinlog utility. But we would like to see on the slave server what queries it is executing in particular time. We have enabled general log to see the queries but we have commit statements only.

How to see the queries executing by replication thread?

We are using MYSQL 5.6.10 for both master and slave.

Queries on slave are being applied by system user, so doing show full processlist and queries running with system user are the ones executed by slave sql thread.

Moreover you can view relay logs with the help of mysqlbinlog utlity and replacing binlog file with relay log file.

You can also introduce triggers on processlist table in information_schema database in this way you would be able to capture queries.

To get current query as string use following statement.

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

To get queries using trigger a sample trigger is as follows.

First create table logging_table with column as varchar(1024).

DELIMITER |

CREATE TRIGGER log_queries_sample BEFORE INSERT ON `table_name`
FOR EACH ROW
BEGIN
    DECLARE original_query_string VARCHAR(1024);
    SET original_query_string = (SELECT info
                                 FROM INFORMATION_SCHEMA.PROCESSLIST
                                 WHERE id >= CONNECTION_ID());
    INSERT INTO `logging_table`(`query`) VALUES (original_query_string);
END;
|
DELIMITER ;

Create triggers of your choice accordingly.

Hope it helps.


MySQL multiple table joins and multiple data levels - best practice I have multiple tables i wish to join up to produce one big query with specific columns summed.
I was having a problem getting the correct summed columns in the final query
(i believe this was because i was due to data being joined to many lines and summing after the join)

is the correct way to handle this type of problem to join on select statements?
This is the way i achieved it but wasnt sure if this is the correct method?

ie

select
    t1.id_a,
    sel_t2.sum_mc
from xyz as t1
    left join
        (select id_a, sum(occ) as sum_mc
        from t2
        group by id_a) as sel_t2
    on t1.id_fh = sel_t2.id_a
group by t1.id_a;

See if this gives you the correct values:

SELECT t1.id_a,
        ( SELECT  sum(occ)
            from  t2
            WHERE  t2.id_a = t1.id_fh
            group by  t2.id_a
        ) AS sum_mc
    FROM xyz AS t1

Note: I am assuming that t1.id_a is unique, so that the GROUP BY is not needed.

(It is confusing to have id_a in both tables, yet not be JOINing on it.)


What if a connection closes in the middle of a commit? I have a few large CSV files to upload to an instance of SQL Server.(Nope, can't use SSMS)
I'm using JDBC to read the CSVs line by line and insert the lines in a table in batches.
Supposing my code is as such:

val connection = DriverManager.getConnection(<urls and credentials>)
connection.setAutoCommit(false)
val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)")
while(<csvReader has next>)
{
    //populate the stmt with values
    stmt.setObject(x,y)
    // add a batch
    stmt.addBatch()
}
stmt.executeBatch()
connection.commit()

Say the CSV file had some 20000 lines with some 50-60 or more columns.
I encountered an error by chance where the connection to SQL Server was lost while connection.commit() was being executed.
This resulted in ~11000/20000 lines being inserted.
I went through my application several times hoping to find some error I might have made. Everything appears to be fine.
My question is, is it possible that if the connection is lost in between committing, partial commits are left inserted?
If so, what could be a way out to save from partial commits?

Short Answer

Add exception handling to ensure a rollback happens on error.

try {
    <code to execute>
} catch (SQLException ex) {
    connection.rollback();
} finally {
    connection.commit();
}

Explanation

if autocommit is set to false, and no connection.rollback() was issued before the program exits, the drivers call an automatic connection.commit() before it closes the connection.


Dropping a CHECK in mariadb? I have a schema migration plan to add json column to mariadb 10.2.
But I am required to have a reverse plan.
Here's the forward plan:

ALTER TABLE `mydb`.`table1`
ADD COLUMN jsonf JSON DEFAULT NULL;

ALTER TABLE `mydb`.`table1`
ADD CHECK(JSON_VALID(jsonf)) ;

Here's the reverse/rollback plan:

ALTER TABLE `mydb`.`table1`
DROP COLUMN jsonf;

DROP CHECK ?

What I am confused is how do I reverse the CHECK?
Thank you.

UPDATE:

I got error

 Kernel error: Error( 4025 ) 23000: "CONSTRAINT `jsonf_is_json` failed for `mydb`.`#sql-8f8_d0c5c1`"

when I try to run add constraint:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT jsonf_is_json CHECK(JSON_VALID(jsonf));

I use mariadb 10.2.8 if that matters

You must assign a name to the CHECK constraint during add:

ALTER TABLE `mydb`.`table1`
ADD CONSTRAINT check_json_validity CHECK(JSON_VALID(jsonf));

Then you may freely remove it by name:

ALTER TABLE
DROP CONSTRAINT check_json_validity;

If you forget to assing the name you may to see it in SHOW CREATE TABLE output.


Unique foreign key constraint between three tables I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed appointment, if known.

These tables seem to cover it:

appointments: id, visitor_id, scheduled_time
attendances: id, appointment_id, arrival_time
missed_appointments: id, appointment_id, reason

How would I add a constraint such that an appointment can't be missed and attended? Is this schema just overcomplicating it?

I would use one table for both completed and missed appointments. Using your notation:

appointments:
id,
visitor_id,
scheduled_time,
status,
CHECK(status in('Scheduled', 'Completed', 'Missed')),
Reason_missed,
CHECK((status='Missed' and reason_missed is not null) or (status<>'Missed' and reason_missed is null)),
unique(id, status)

attendances:
id,
appointment_id (nullable),
arrival_time,
appointment_status,
check((appointment_id is null and appointment_status is null) or (appointment_id is not null and appointment_status in('Scheduled', 'Completed'),
foreign key(appointment_id, appointment_status) references appointments(id, status) on update cascade


Mysql 5.6 Replication Lag fluctuating between 0 and n I have one master and 7 slaves. During high load on my master, I see replication lag and it keeps fluctuating between 0 and n (where n keeps increasing with time and I have seen n grow more than 1 hour). Fluctuations happen in a matter of seconds i.e. sec:1 - Lag:0s, sec:2 - Lag:2000s, sec:3 - Lag:0s, sec:4 - Lag:2002s,

When seconds_behind_master is 0; show slave status\G says: "Slave has read all relay log; waiting for the slave I/O thread to update it".
When seconds_behind_master is n; show slave status\G says: either "Reading event from the relay log" or "System Lock".
On Master "show processlist" tells the replication thread has status "Sending binlog event to slave" always.

With the above points, I have figured that my SQL thread is not lagging and it's the IO thread which is the culprit. I read that network slowness can cause this issue, but network is not a bottleneck, as I have verified the bandwidth used between master and slaves is only 50%. When I turned on slave_compress_protocol, network usage went down but I was still seeing the replication lag grow in a fluctuating fashion.

I want to know what can be other causes apart from network which can cause this issue. I have gone through: https://www.percona.com/blog/2013/09/16/possible-reasons-when-mysql-replication-lag-is-flapping-between-0-and-xxxxx/ and couldn't attribute my lag to any of the points mentioned in the post.

Also, when the load on master stops, replication lag stops fluctuating and starts decreasing steadily from n and finally catches up.

Thanks.

Edit:

Can it happen that due to heavy load on master (% CPU utilisation is hitting 100%), IO thread is waiting intermittently to read from the binlogs)?

Seconds_behind_master bouncing between 0 and some 'large' value?

I have seen that scenario for 16 years.  I have never found the cause or cure.  The problem usually goes away after a day or two.

Bottom line:  Ignore it.


Huge Analysis Services log file: msmdsrv.log I've just discovered our prod analysis services is writing to a log that is 1.6 GB in size.

I was able to get a copy of the log file saved off to an alternate location and open it in WordPad (Notepad, Word, etc.. said the file was too large).  It appears this log has been churning along for almost 4 years without a problem.  But now the problem is that it is too big.  The msmdsrv.ini file doesn't appear to have a setting for rolling over the log or limiting it's size.

What is the best approach to preventing this from happening in the future? How do you properly manage your Analysis Services log file?

You can't recycle the SSAS log in a similar manner to SQL Server. You need to do this manually by building a script and then scheduling it to run either inside of SQL Agent or Windows Task Scheduler.

Here is a sample Powershell script you can use:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

#Get a list of installed SSAS instances on this server that are running
#We need a service to be started in order to rename the log file
$SSASServices = Get-WmiObject -query "select * from win32_service where DisplayName LIKE 'SQL Server Analysis%' and State = 'Running'"

#for each installed instance, update the logfile name

ForEach ($sname in $SSASServices)

{

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($sname.SystemName)

#gets current filename
$SSASErrorFileName = ($SSASServer.Serverproperties | where-Object {$_.Name -eq "Log\File"}).Value

#split out the .log extension
$SSASErrorName=$SSASErrorFileName.Split(".")[0]

#rename the errorlog, appending the current date
$ErrorLogName = "msmdsrv_{0}.log" -f (Get-Date -Format 'MM_dd_yyy_hh_mm_ss')

$SSASServer.ServerProperties['Log\File'].Value = $ErrorLogName
$SSASServer.ServerProperties['Log\File'].PendingValue = $ErrorLogName
$SSASServer.Update()

Restart-Service $SSASServices.Name

}

I blogged about it at http://thomaslarock.com/2015/01/how-to-recycle-the-sql-server-analysis-services-msmdsrv-log-file/ as well.

HTH


Undo DDL statement on View in Oracle I Want to undo DDL changes on View, how to do that?

I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If there is any chance to use logminer without setting this parameter(without restarting database) ?

Waiting you suggestions, thank you

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago.  You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.


Clarify - Need to add Multiple foreign key constraints I am not sure if I am thinking about this process the wrong way.

Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a particular round and date.

I have a Fk from PlayersTeams - A junction table that defines which players play for what teams.

A round table Fk - defines what round the list is for

In the TeamList table I have the two aforementioned columns, plus a date (mm/dd/yyyy) column, a TeamName column, and 17 player position columns.

To ensure that each player in the team list is only from the same team do I need 17 foreign key constraints from PlayersTeams(Fk) for each position to ensure that only a player from the selected team can be selected in a given position?

have I got something minged up?

Edit as below.

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[Junct_PlayersTeams]    Script Date: 05/02/2012 14:11:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Junct_PlayersTeams](
    [TeamPlayer] [int] IDENTITY(1,1) NOT NULL,
    [fk_Players] [int] NULL,
    [fk_Teams] [int] NULL,
    [startDate] [datetime] NULL,
    [endDate] [datetime] NULL,
 CONSTRAINT [PK_Junct_PlayersTeams] PRIMARY KEY CLUSTERED
(
    [TeamPlayer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Players] FOREIGN KEY([fk_Players])
REFERENCES [dbo].[Players] ([PLayerID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Players]
GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Team] FOREIGN KEY([fk_Teams])
REFERENCES [dbo].[Team] ([TeamID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Team]
GO

Team List Table

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[TeamList]    Script Date: 05/02/2012 14:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TeamList](
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [Fullback] [int] NULL,
    [LeftWing] [int] NULL,
    [LeftCentre] [int] NULL,
    [RightCentre] [int] NULL,
    [RightWing] [int] NULL,
    [FiveEighth] [int] NULL,
    [HalfBack] [int] NULL,
    [Lock] [int] NULL,
    [SecondRow(L)] [int] NULL,
    [SecondRow(R)] [int] NULL,
    [Prop(L)] [int] NULL,
    [Hooker] [int] NULL,
    [Prop(R)] [int] NULL,
    [Interchange1] [int] NULL,
    [Interchange2] [int] NULL,
    [Interchange3] [int] NULL,
    [Interchange4] [int] NULL,
 CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED
(
    [TeamListID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junc_TeamCoach] FOREIGN KEY([Coach(Fk)])
REFERENCES [dbo].[Junc_TeamCoach] ([Junct_TeamCoach])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junc_TeamCoach]
GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junct_PlayersTeams] FOREIGN KEY([Team(Fk)])
REFERENCES [dbo].[Junct_PlayersTeams] ([TeamPlayer])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junct_PlayersTeams]
GO

Instead of one column per position in TeamList, you can use lookup tables for Positions and Players. The TeamList table then defines which player is in which team and in what position for each round. You then don't need to enforce integrity on all players being from the same team because there is one row per player that defines the player:team relationship in TeamList (playerID and teamID).

I've included some DDL below as an example - you may need to add more data columns,etc. but it should be a good starting point.

Positions table:

 CREATE TABLE [dbo].[Positions]
    (
        [PositionID] [int] NOT NULL,
        [position_name] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED
    (
        [PositionID] ASC
    )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

The players table can be created as (not including team associations, but you could add this here):

CREATE TABLE [dbo].[Players](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [player_name] [varchar](500) NULL,
PRIMARY KEY CLUSTERED
(
    [playerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

You can then create your TeamList table like so

 CREATE TABLE [dbo].[TeamList]
    (
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [PositionID] int null,
    [PlayerID] int not null,
    CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED
    (
    [TeamListID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Positions] FOREIGN KEY([Position])
    REFERENCES [dbo].[Positions] ([PositionID])
    GO

    ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Positions]
    GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[Players] ([playerID])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Players]
GO

-- Original FK definitions on round, team, coach, etc. go here


Classic fool who has default Full Backup model and out-of-control log file ---- how to I get this controlled? Hey there I'm usually more of a development dba but the old dba left --- and he left about a year ago without a replacement.
I'm finally actually looking under the hood of some of these servers -- I'm not a production dba but I ran some diagnostics I found online and they say I have big problems.
Anyway I'm learning this stuff slowly but this seems like a common issue --- I have Full Recovery mode on a particular database, but never back it up -- as a result the log file grows exponentially -- I take it?
What are my options to get rid of this?
I'm guessing:

If I do a full back up or schedule one, the log file will get cleared after this?

Can I just change to the simple recovery model? I know that has recovery implications - I'm comfortable with data loss as this is a BI database. Will that flush out the log file immediately as well? Do I have to do anything OTHER than a switch to the simple recovery model?

Is there some way to monitor the progress of either of these two options? I'm guessing either will cause the database to start chugging resources doing something or other.
Thanks!

Do I have to do anything OTHER than a switch to the simple recovery model?

If you have a full backup, and don't need point-in-time recovery, simply switch the database to Simple recovery.  After the next Checkpoint you should be able to shrink the log file.  If the active VLF happens to be at the end of the file, rebuild an index or two, and try again.


How are multiple my.cnf's loaded by MySQL? If I want to replicate how the MySQL/Maria client reads ~/.my.cnf, /etc/my.cnf, etc, how would I do it?

The MySQL docs give a list of paths and mention "top files are read first, files read later take precedence", is that on a key-value basis or the entire file?

The standard config file format applies. It is on group and key basis.

So if you have:

[mysql]
some_variable = 1

[client]
some_variable = 3

and after another file has

[mysql]
some_variable = 2

Then some_variable under mysql will be 2 but under client it will stay 3.

  The only relevant part for MySQL server is the [mysqld] though.

The easiest way to determine which files are being read is to strace the mysql process on startup.

Start MySQL with the normal init script and run ps -ef|grep mysqld.
Copy /usr/sbin/mysqld
Then run strace on the command grepping .cnf.

You should see something like this:

$ strace /usr/sbin/mysqld --basedir=/usr --datadir=/databases/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql-error.log --pid-file=/databases/mysql/data/server.local.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 2>&1 |grep .cnf
stat("/etc/my.cnf", 0x7fffa6edeb40)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=5454, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY)     = 3
stat("/usr/etc/my.cnf", 0x7fffa6edeb40) = -1 ENOENT (No such file or directory)
stat("/root/.my.cnf", {st_mode=S_IFREG|0644, st_size=73, ...}) = 0
open("/root/.my.cnf", O_RDONLY)         = 3
stat("/root/.mylogin.cnf", 0x7fffa6edeb40) = -1 ENOENT (No such file or directory)
stat("./auto.cnf", {st_mode=S_IFREG|0660, st_size=56, ...}) = 0
open("./auto.cnf", O_RDONLY)            = 13
stat("/root/.mylogin.cnf", 0x7fffa6edeb40) = -1 EACCES (Permission denied)


Oracle - How can I find the session id of a shell script (Linux)? I have a Linux script run periodically from crontab which executes some sql scripts.  How can I get/identify the session of that running job?

thanks

In order to find a specific session running a certain script, it is easiest if the scripts makes itself identifiable by using dbms_application_info and dbms_session. See Morgans Library for details. Also see track the parts of my application that are in use for sample code.

Doing so enables you to select on v$session and filter not only on username and machine but also on module, action, client_info and client_id. Username and machine are not mutable but the other columns are controlled by dbms_application_info and dbms_session. Using those makes your script recognizable. This of course, only works when the other session has select privileges on v_$session.

dbms_application_info is also great for finding where your code is running; a way to instrumentate your code to make performance analysis easier.

your code in SQLdeveloper would be as simple as

select sid
from gv$session
where module = 'what you specified'
order by sid
/

The code added in the script to make this work would be

exec dbms_application_info.set_client_info('some detail');
exec dbms_application_info.set_module('what you specified','action');
exec dbms_session.set_identifier('DDD');


Create a function/tsql to get strings from the 1st table/data and compare and convert if from the value of second table I want to create a function/tsql to get strings from the 1st table/data and compare and convert to the value of a second table if the strings match what is on table 2.

Business example.
When a cashier mistakenly encoded ABC1231 as company name instead of ABC the report will have an issue showing ABC1231 instead of ABC. We need to correct this by matching it against a master list of company names in another table.

The query logic Im trying to apply here is to match how many similar strings are in table 1 data to master list data in table 2. Then if the process saw that the strings from table 1 has a match or has the highest similar string it will output what data in table 2. This should be also case sensitive and allows int and special character. See example below.

Example:
Table 1 contains ->

A121B312C33,AB12312C,AB-C,ABC223,AsBCD21,D23EF,D2E2F2,DEF

Table 2 contains ->

ABC,DEF

Output should be:

ABC,ABC,ABC,ABC,ABC,DEF,DEF,DEF

It is not only capital letters. It should match what is on the 2nd table whether lower case or upper case with special characters or with integer as well.

Byron,

Following our small conversation in the comments, I would recommend making a table structure change instead of trying to make a code change to accommodate this.

I believe this is the solution for you because it will:

Limit the possibility for this kind of mismatch to happen again in the future
Eases the level of effort to correct an issue in the future if it comes up again
Removes the need for any complicated Macros or Queries to retrieve the data you are actually looking for

This will however require a couple of steps of initial effort from you:

Make table changes
Make application changes to support both the new table structures and probably a slight change to data entry processes
Data Cleanup Project

1. Make Table Changes

One thing Relational Databases are really good at is creating was I have heard call either a Master List or an List Of Values (LOV) table.  The benefit is this forces your data and your application to only store certain allowed value for data integrity purposes.  It then also allows you to make a change in one place and have it update everything.

Let's assume your two tables look something like this:

Table_1
ID INT,
Company VarChar(50),
CreateDateTime DATETIME

Table_2
Company VarChar(50)

If we rebuild the tables to be something like this we get to create a Foreign Key relationship which will ensure that the data stays consistent over time. (Table_1.CompanyID must be a value found in Table_2.CompanyID)

Table_1
ID INT,
CompanyID INT,
CreateDateTime DATETIME

Table_2
CompanyID INT,
CompanyName VarChar(50)

2. Make application changes to support both the new table structures and probably a slight change to data entry processes

When you wanted to create the original output of Table_1 you would run a query like below.  This kind of change would need to be made in anything that tries to retrieve this data (reports, applications, automated processes, etc...)

SELECT T1.ID,
T2.CompanyName,
T1.CreateDateTime
FROM Table_1 T1
    INNER JOIN Table_2 T2
        ON T1.CompanyID = T2.CompanyID

When it comes to Insert and Update processes, you will first need to know that whatever the CompanyName value is you are trying to store.  If that value does not exist already, than you have to create that value in Table_2 before writing your record to Table_1.  For the sake of simplicity lets assume that the value ABC already exists and Table_1.ID is an Identity/Auto-Incriminating column (IE: I don't need to create a value for it on the Insert).  A simple Insert and Update script would look something like:

--Insert
INSERT INTO Table_1 (CompanyID, CreateDateTime)
SELECT CompanyID,
GETDATE()
FROM Table_2
WHERE CompanyName = @CompanyName

--Update
DECLARE @CompanyID INT
SET @CompanyID = (SELECT TOP(1) CompanyID
                    FROM Table_2
                    WHERE CompanyName = @CompanyName
                )

UPDATE Table_1
SET CompanyID = @CompanyID
FROM Table_1
WHERE ID = @Table_1ID

Your application will probably need to have some sort of separate screen that allows certain users to create new Table_2 records when appropriate.  Otherwise you may have a similar problem down the road with just a different implementation.  You can do a check and when needed insert into Table_2 when trying to run insert or update operation for Table_1.  I can provide some examples for those if you would like but I would recommend against it.

3. Data Cleanup Project

Depending on the amount of data in your database, this may be the biggest effort.  You will need to take the data you have right now and transpose it into this new table structure.

--Create Table_2 Records
INSERT INTO Table_2 (CompanyName)
SELECT DISTINCT Company
FROM Table_1_Original

--Create Table_1 records with correct references to values in Table_2
INSERT INTO Table_1 (CompanyID, CreateDateTime)
SELECT T2.CompanyID,
T1O.CreateDateTime
FROM Table_2 T2
    INNER JOIN Table_1_Original T1O
        ON T2.CompanyName = T1O.Company

Then correct the records in Table_2 so that only the correct values you are looking for exist.  You could do this directly in the original Table_1 before running the above script or make changes on Table_2 directly after everything is said and done.  There are a couple of different ways to make it happen and I can provide some guidance if needed.

When This is All Said and Done

You should be in a position where your application self-regulates much better and your reporting process is as simple as a single SELECT with a simple JOIN and no need for any kind of conversion in either SQL or Excel.

Hopefully this helps.  Let me know if this solution won't work for some reason and we can see about creating a different solution.


What is the Cassandra setting for coordinator timeouts for schema change queries? In my testing, I've found that the write_request_timeout_in_ms setting does not have any bearing on the timeouts for CREATE TABLE or ALTER TABLE statements in CQL. For instance, I have it set to 10000ms but I've found that some CREATE TABLE statements can take around 12 seconds to complete. So what's the correct setting?

According to a Datastax representative, the setting which affects schema changes is request_timeout_in_ms.

It should be noted that these timeout settings are not hard timeouts from the time your client application connects to the coordinator node. If the connection is lost, then the timeout counter restarts. Hence why I was seeing some CREATE TABLE commands taking 12 seconds even though I had request_timeout_in_ms set to 10000ms.


PostgreSQL LIKE query on ARRAY field Is there any way to have a Postgres LIKE query on a ARRAY field?

Currently I want something like that:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Currently lower is not needed that much. However it should find ONE matching field inside the ARRAY. Is that even possible?

Currently I use a materialized view to generate the "list" table with a JOIN and a ARRAY_AGG(), since I JOIN a table where more values could be on the right table. Which would duplicate fields on the left table, which is not what I want.

Edit this is how I create the view (really sluggish and ugly):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id,
       a.oa_nr,
       a.date_deleted,
       a.lock,
       a.sds_nr,
       a.kd_art_nr,
       a.kd_art_index,
       a.kd_art_extend,
       a.surface,
       a.execution,
       a.surface_area,
       a.cu_thickness,
       a.endintensity,
       a.drilling,
       array_agg(o.id::text) AS offer_list
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr
GROUP BY .....;

I also need to return the IDs of the task_offer table.

You can use unnest() like @dezso already mentioned, for instance with a LATERAL join (Postgres 9.3+)

SELECT l.*
FROM   list l, unnest(array_field) a  -- implicit lateral
WHERE  lower(a) LIKE '1234%';

You don't need any of this for the presented case. No materialized view at all. This query on the underlying tables is faster, because it can use an index:

SELECT *  -- or selected columns
FROM   article_list a
JOIN   LATERAL  (                      -- only matching IDs
   SELECT array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  article = a.oa_nr            -- LATERAL reference
   AND    id::text ILIKE '1234%'       -- or just LIKE
   ) o ON offer_list IS NOT NULL;

offer_list is an array of the original data type in my result, and contains only matching IDs.

Returns a single row from article_list with an array of matching IDs in task_offer. Add a text_pattern_ops index (for left-anchored matches) to get best read performance:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) text_pattern_ops);

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Or a trigram index for infix matches (not left-anchored):

How is LIKE implemented?

Or maybe a plain join for selective patterns:

SELECT a.*, o.offer_list -- or selected columns
FROM   article_list a
JOIN   (                          -- only matching IDs
   SELECT article, array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  id::text ILIKE '1234%'  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;

Needs an index on article_list, too. (Which you probably have - no information in the question.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);


Help writing a query to transform rows with json data into 1 download file I want to write a query for a table like this:

id  job_id  data (bytea)
--------------------------
 1       1  {"hello": "John"}
 2       1  {"goodbye": "Moon"}
 ...
100      2  {"also": "Hi"}

I want to be able to ask:

> Give me all the data for job #1

and have the text file automatically built like this:

{"hello": "John"}
{"goodbye": "Moon"}

My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it. This seems to work:

    select encode(data, 'escape') from mytable where id='1'

But I would rather not do 2 queries (one to get all rows - and another to get data). Can I issue a single query, and somehow paginate, one row at a time? Number of rows can be large, e.g. 100k.

Any tips to point me in the right direction are much appreciated.

P.S.: Order is not that important to me. E.g. this is ok too (if this helps):

{"goodbye": "Moon"}
{"hello": "John"}

Don't store JSON as bytea. Store it as jsonb or json.

Maybe I don't understand your question

ALTER TABLE table
  ALTER COLUMN data
  SET DATA TYPE jsonb USING encode(data, 'escape')::jsonb;

Then just do.. SELECT * FROM table WHERE job_id = 1

  My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it.

Why? What does too large mean there? The way this works, you'll only encode one row at a time and send it to the client...


Disk usage not decreased after the full and log backup in SQL Server I am using SQL Server 2012. I created the backup (full and transaction log backup) using maintenance plan and scheduled the job using SQL Server agent.

After the success of the backup, I cannot see why the C partition on my disk is not reduced. What I know is that after the backup of logs, the disk will be reduced.

Forgive me for the ignorance but I did my search. Plus, I am coming from Oracle database background.

Is there anything wrong? Under what circumstances the disk size is not reduced after log backup?

Full backups have nothing to do with the size of the database files.  Log backups only peripherally affect log file size.  If your database is in full recovery mode then the data in the log file will remain until a log backup is taken.  This means that as more data is written to the log file additional space will be required.

Think of it as a bucket.  Over time water is added to the bucket.  Every time you take a picture of the bucket (log backup) you can pour the water out.  Regardless of how full the bucket is it remains the same size.

Now let's say you have a large transaction (requiring a lot of water) or you haven't been taking your log backups frequently enough.  SQL wants to add water to the bucket but it's now full.  You now need a bigger bucket.  So SQL goes out and gets a bigger bucket.  Now your bucket is larger.  You take a log backup and the bucket is emptied.  The bucket doesn't shrink back to it's original size.  Your regular usage may now only fill a small portion of the bucket but that again doesn't affect the size of the bucket.

Note I'm way over simplifying. There are other things that can cause your bucket not to empty.


Drop a replicated subscription table that has been orphaned after restore migration We restored a database on a new server for a migration.

When attempting to set a subscription to a transnational publication, we get the error:

  Can't drop the table because it is set for replication.

However, it is not being replicated to.

I have already set several push subscriptions up from this server that go to another so I don't want to completely wipe out all replication and rebuild everything.

I was wondering if there is a place that I could delete the piece of information that makes the article think it is still replicated to, so that I can add it back to a subscription

Date        1/29/2018 3:42:13 PM
Log     Job History

Step ID     1
Server      Server1Pub
Job Name        RepJobName
Step Name       Run agent.
Duration        00:00:05
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
2018-01-29 21:42:13.174 Copyright (c) 2016 Microsoft Corporation
2018-01-29 21:42:13.174 Microsoft SQL Server Replication Agent: distrib
2018-01-29 21:42:13.174
2018-01-29 21:42:13.174 The timestamps prepended to the output lines are expressed in terms of UTC time.
2018-01-29 21:42:13.174 User-specified agent parameter values:
            -Publisher Server1Pub
            -PublisherDB DBPub
            -Publication PubName
            -Distributor Server1Pub
            -SubscriptionType 1
            -Subscriber Server2Sub
            -SubscriberSecurityMode 1
            -SubscriberDB db1
            -XJOBID 0x466A0D3EF212C442B2B0E6521144ED71
            -XJOBNAME ReplicationJobName
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER Server2Sub
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006F44
            -XParentProcessHandle 000000000000592C
2018-01-29 21:42:13.174 Startup Delay: 2294 (msecs)
2018-01-29 21:42:15.474 Connecting to Subscriber 'Server2Sub'
2018-01-29 21:42:15.509 Connecting to Distributor 'Server1Pub'
2018-01-29 21:42:16.279 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 1
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2018-01-29 21:42:16.864 Initializing
2018-01-29 21:42:17.239 Snapshot will be applied from the alternate folder '\\192.168.5.124\Replications\unc\Folder'
2018-01-29 21:42:17.914 Agent message code 3724. Cannot drop the table 'dbo.table' because it is being used for replication.
2018-01-29 21:42:18.144 Category:COMMAND
Source:  Failed Command
Number:
Message: drop Table [dbo].[table]

2018-01-29 21:42:18.194 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:  3724
Message: Cannot drop the table 'dbo.table' because it is being used for replication.

When you migrate the database all the information goes together.

Try this at the new server:

USE master
GO

EXEC sp_replicationdboption @dbname = N'YOUDATABASENAME',
                            @optname = N'publish',
                            @value = N'false';
GO

EDIT

OK, my first answer will kill all replication at the new server.

This will kill the subscriber at the table/article and after drop the article itself from replication:

USE YOUR_DATABASE
GO

EXEC sp_dropsubscription @publication = N'YOUR_PUB',
                         @subscriber = N'SUBSCRIBER_SERVER',
                         @destination_db = N'DATABASE_DSESTINATION',
                         @article = N'TABLE';
GO

EXEC sp_droparticle @publication = N'YOUR_PUB',
                    @article = N'TABLE',
                    @force_invalidate_snapshot = 0;
GO


Different plans or buffers for different connections? We had an issue in our dev environment where a procedure call timed out from the web server after 30 seconds. I traced the query and ran it manually (same params and all) from SSMS and it executed in about 2 seconds. I then ran

dbcc dropcleanbuffers
dbcc freeproccache

and after that the call from the web server also completed in time just fine.

I would suspect that the same plan should be used since I was using the exact same parameters from both connections, but I'm not sure.

My question is: Can there be different plans or buffers for different connections? Or could it have been some other side effect caused by me running the above dbcc-commands?

When you say "same params and all" then you may find different credentials were used or the same compiled plan wasn't re-used based on timing.

I suspect here that the first web call and the SSMS call used different plans. The DBCCs then cleared the plan, so the second web call worked as expected

There are criteria that determine plan re-use such as using schema on all object references (eg dbo.table not table) which will be affected by different credentials.

However, the main culprit is usually parameter sniffing. See

Does restarting SQL Server speed it up?
Suddenly Slow Execution Plan for Stored Proc
Code creates different plan when ran ad-hoc vs. in a stored procedure

Failing that, some SET options may be different too:

Why would SET ARITHABORT ON dramatically speed up a query?

Note: these follow through to good background reading...


How do I dump a Postgres table and restore under another user/role? I am getting an error when I try to restore a Postgres database table to a different user/role than from which it was dumped.

I am doing the pg_dump as OS user: 'postgres', and doing the restore as OS user 'postgres'.

The databases however were created as other users, not by the postgres user.

Error when running pg_restore script:

CREATE TABLE
psql:./person.sql:49: ERROR:  must be member of role "dev"
psql:./person.sql:57: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:./person.sql:58: error: invalid command \N

I do not get this error if I dump/restore to any database created by the same user/role ( 'dev' ).

I only see it when I dump a table created by one user, and then try to restore under another database created by a different user/role.

Here is my dump script ( $./dump-table )

#!/bin/bash

PGUSER=dev
PGPASSWORD=<password>
PG_DATABASE=db_dev
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql

rm -f ./*.sql
pg_dump \
   --format plain \
   --verbose \
   --file $SQL_FILE \
   --table $TABLE $PG_DATABASE

Here is my restore script ( $./restore-table )

#!/bin/bash

PGUSER=prod
PGPASSWORD=<password>
PGDATABASE=db_prod
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql
psql -1 -f ./$SQL_FILE

Is there another parameter I need to use when dumping, or restoring the table to avoid this error?

Use the --no-owner option of pg_dump to skip the ALTER ... OWNER TO ... statements.


Selecting a group from groups based on one row condition There is a group of rows which have the losscode and service odom. The contractnumber and service odometer are used to group the rows.

Please, may you help me to find the way to select only the row group which have the slosscode='REVSTD'?

I assume you want all rows of the group(s) where at least one row of the group has sLossCode = 'REVSTD'. In this case, you don't need GROUP BY. You can use either a self join (but that would only work under certain UNIQUE constraint requirements) or an EXISTS subquery:

SELECT s.sContractNumber, s.sLossCode, s.iServiceOdom
FROM service_table AS s
WHERE EXISTS
      ( SELECT 1
        FROM service_table AS q
        WHERE q.sLossCode = 'REVSTD'
          AND q.sContractNumber = s.sContractNumber
          AND q.iServiceOdom = s.iServiceOdom
      ) ;


Problem with dropping materialized views and mv logs taking a long time (or never) to complete Apologies if it's a simple question, DBA is not my main area of expertise!

I have a materialized view that's fast refreshable, joining two tables.

When I try to drop either the MV itself or logs associated with constituent tables, it starts the process but never comes back.

I have tried truncating the log tables and MV table, and then dropping the MV, but it's the same.

There's an entry in dba_summaries for the MV.

Any clues as what is stopping Oracle from dropping it?

EDIT even this doesn't execute / return :

ALTER MATERIALIZED VIEW  STAGING_OWNER.CONTRACT_SUMMARY_MV_FR  REFRESH COMPLETE;

Version information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

You can do:

truncate table STAGING_OWNER.CONTRACT_SUMMARY_MV_FR ;

This simply will flush the MV. Then:

dbms_mview.refresh('STAGING_OWNER.CONTRACT_SUMMARY_MV_FR',method=>'C',atomic_refresh=>FALSE);

this will speed up mview rebuild. If you still experience any troubles you must trace event 10053 to understand why refresh is taking so much time.


Restore SQL Server database from .sql files So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git

The problem is they're saved as .sql files.

Is there anyway we can batch restore our schema from directories full of these files?

I've looked around and I can only find tutorials for restoring from .bak files or .mdf's. This isn't the lazy man's way out- I just need to find a solution ASAP. Any advice or resources/ anything at all would be greatly appreciated.

Thanks Interwebs,

Dylan

So you just need to execute the collection of schema scripts to recreate your database? You could execute them one by one or create a batch file and use SQLCMD to execute all script files in directory

for /r "%ScriptPath%" %%X in (*.sql) do (
   sqlcmd -S%ServerName% -d%DBName% -b -i "%%X"
)


Can I physically split a SQL Server database? I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate.

I have a database that has a dozen or so tables that reflect statistics for the last 'n' years. The tables are called History2007, History2008 etc and the current year's table is just called History. All the previous years tables contain static data (won't be changed) and I would like to move these files to another file that is linked to the main database. As the old data tables contain foreign keys to Articles, Clients and Suppliers etc I can't just copy these tables to a new database.

The whole idea is to reduce the size of the database and the backups as I can make one backup of the 'old' data and that will not change until next year.

Any thoughts? Ideas? Guidance?

Thanks,
Martin.

Yes, is possible to move old data to filegroups that are still part of the database, but store on files located on cheaper, slower, storage, adnd mark these file groups as read-only. You still need to do a full database backup once, but subsequent backups can leave the read-only file groups out of the backup, significantly reducing the size (and time!). I recommend you read Performing Piecemeal Restores.

Another useful feature is partitioning, which allow you to split a table automatically into partitions and place partitions onto this slow, untouched and read-only file groups. See How to Implement an Automatic Sliding Window in a Partitioned Table.


Change SELECT DISTINCT to UPDATE DISTINCT How can I modify my SELECT DISTINCT query to an UPDATE DISTINCT query?

It is important that it only updates the distinct records, as there are multiple records associated with each [Finance_Project_Number] (due to CRUD operations). I only want to update a single record, as this will simply set in motion a different process of validating the data etc.

If there are multiple records collapsed into a single row as a result of the DISTINCT, any of these can be updated - it does not matter.

When I run my select query, I get a result of: 6 982:

SELECT DISTINCT
    [Finance_Project_Number]
FROM [InterfaceInfor].[dbo].[ProjectMaster]
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM [IMS].[dbo].[THEOPTION]
        WHERE
            [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] =
            [IMS].[dbo].[THEOPTION].[NAME]
    );

Here is my attempt at converting my query to a DISTINCT UPDATE query, but this updates 15 353 records:

UPDATE [InterfaceInfor].[dbo].[ProjectMaster]
SET
    [Processing_Result_Text] = 'UNIQUE',
    [Processing_Result] = 0
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM [IMS].[dbo].[THEOPTION]
        WHERE
            [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] =
            [IMS].[dbo].[THEOPTION].[NAME]
    );

To just update an arbitrary one from each distinct group you could use

WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number]
                                       ORDER BY (SELECT 0)) AS RN,
                [Processing_Result_Text],
                [Processing_Result]
         FROM   [InterfaceInfor].[dbo].[ProjectMaster]
         WHERE  NOT EXISTS (SELECT *
                            FROM   [IMS].[dbo].[THEOPTION]
                            WHERE  [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] = [IMS].[dbo].[THEOPTION].[NAME]))
UPDATE T
SET    [Processing_Result_Text] = 'UNIQUE',
       [Processing_Result] = 0
WHERE  RN = 1;

If you decide there is some criteria by which the row to be updated should be selected after all simply change the ORDER BY (SELECT 0) accordingly so that the desired target row is ordered first - e.g. ORDER BY DateInserted desc would update the latest one as ordered by a column called DateInserted if such a column exists.

This uses a common table expression (CTE) because it is not permitted to reference ranking functions such as ROW_NUMBER directly in the WHERE clause. It is allowed to update data via common table expressions in the same circumstances as for updatable views (basically the data being updated must be able to be mapped back straight forwardly to specific items in a single base table).

If you are not yet familiar with ranking functions you may well find SELECT-ing from the CTE first to be beneficial.

CREATE TABLE #TheOption(NAME VARCHAR(50));

CREATE TABLE #ProjectMaster
(
Finance_Project_Number VARCHAR(10) NOT NULL,
Processing_Result_Text VARCHAR(50) NULL,
Processing_Result INT NULL
);

INSERT INTO #ProjectMaster (Finance_Project_Number, Processing_Result_Text)
VALUES ('A00001', 'A'),
       ('A00001', 'B'),
       ('A00001', 'C'),
       ('B99999', 'D'),
       ('B99999', 'E'),
       ('C47474', 'F'),
       ('C47474', 'G');

INSERT INTO #TheOption (NAME) VALUES('C47474');

WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Finance_Project_Number
                                       ORDER BY (SELECT 0)) AS RN,
                Finance_Project_Number,
                Processing_Result_Text,
                Processing_Result
         FROM   #ProjectMaster pm
         WHERE  NOT EXISTS (SELECT *
                            FROM   #TheOption opt
                            WHERE  pm.Finance_Project_Number =opt.NAME))
SELECT *
FROM T
ORDER BY Finance_Project_Number, RN;

Example results are below

The C47474 rows are filtered out as they do exist in the other table so don't meet the NOT EXISTS, the remaining rows are grouped by Finance_Project_Number and assigned a sequential number within each group.

In this case the yellow rows would meet the RN = 1 condition and be updated. However there is no guarantee exactly how these numbers will be assigned within each group unless you use a ORDER BY clause on an expression guaranteed to be unique. Without this it could potentially change even between successive executions of the same statement.


Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7 I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code below but it failed:
ALTER TABLE db.video_info MODIFY COLUMN created_time bigint(20)
  DEFAULT (ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)) NULL;

MySQL 8.0 supports the expression default like you show.
MySQL 5.7 does not support expressions as default. Only NULL, or a constant value, or CURRENT_TIMESTAMP if it's a DATETIME or TIMESTAMP column.
For MySQL 5.7, you have two alternatives:

Declare the column as created_time datetime(3) default current_timestamp(3).

Use BIGINT as you are doing, but write a trigger to set the value.


how to view data as history of field value change Example

i have data like

id   employeeId Salary       Year
1      1           12000     2001
1      2           10000     2001
1      1           16000     2002
1      3           12000     2004
1      1           18000     2005
1      3           14000     2006

So employee ID 1 has 3 records in this and 2  has one and 3 has two record
i expect output as

EmployeeID  NewSalary Oldalary
1             18000    16000
1             16000    12000
2             10000    -
3             14000    12000`

This should work on SQL Server 2012 +

   ;with cte as
    (select employeeId,
            Salary AS 'NewSalary',
            lead(salary,1,0)over(partition by employeeid order by salary desc) as 'OldSalary',
            row_number()over(partition by employeeid order by salary desc) as rn
     from Table_2
     )

     select employeeId,
            NewSalary,
            OldSalary
     from cte
     where OldSalary <> 0 or (OldSalary = 0 and rn=1)


Concatenate with NULL values ( Should not replace NULL ) After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is:

Table:

create table nulos ( nome varchar(max))
alter table nulos add cidade varchar(max)

values:

insert into nulos values ( 'rafael','são paulo'),('juliana',null)

I need to concatenate the values inside this table, to a insert, like this:

 select 'insert into nulos (nome,cidade) values ('+nome+','+cidade+')'
from nulos

And the result of the select:

insert into nulos (nome,cidade) values (rafael,são paulo)
NULL

how can I use the NULL value inside this concatenation? every answer says something to replace the null with '' or '_', But what I need it this:

insert into nulos (nome,cidade) values (rafael,são paulo)

insert into nulos (nome,cidade) values (Juliana,NULL)

SET CONCAT_NULL_YIELDS_NULL on is not an option because it just deletes the NULL value, and I need to insert it.

Wrap your strings in single quotes so they will be input as strings.  The NULL does not need quotes.

SELECT 'INSERT INTO nulos (nome,cidade) VALUES (' +
    CASE WHEN nome is null then 'NULL' ELSE ''''+
        REPLACE(nome,'''','''''')+'''' END +
    ',' +
    CASE WHEN cidade is null then 'NULL' ELSE ''''+
        REPLACE(cidade,'''','''''')+'''' END +
    ')'
FROM nulos

This will give you:

INSERT INTO nulos (nome,cidade) VALUES ('rafael','são paulo')
INSERT INTO nulos (nome,cidade) VALUES ('Juliana',NULL)


Choosing a RULE instead of a TRIGGER I my current database schema, there are hundreds of tables that reference another single table (called transaction_entry), that stores generic information about each row (when it was created, last updated, who created, who updated, and so on). For instance consider this (simplified) schema:

CREATE TABLE transaction_entry (
   id integer,
   created timestamp,
   modified timestamp);
CREATE TABLE person (
   id integer,
   te_id integer REFERENCES transaction_entry(id),
   name text);

Currently, we update this information on the client side (python), but I am considering moving this to the database itself, either using triggers or rules.

This should behave so that every time one row is updated, the referenced transaction_entry should be updated as well. Most of the time a single row is updated in the queries, but there are rare cases that a lot of rows will be updated by a single query.

The postgres documentation does state that:

    If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule

This statement alone should make me decide that a TRIGGER is the correct choice for my case, but I did test with a RULE as well, and that does work fine. This is the rule that I tested with (also simplified):

CREATE RULE foo AS ON UPDATE TO person DO ALSO
   UPDATE transaction_entry SET modified = statement_timestamp() WHERE id = OLD.te_id;

What is the best choice for this scenario? Is a TRIGGER really the only option here, since each row has its on transaction_entry or is a RULE a safe option as well? What about performance? Should there be a significant difference between those two for this case?

I am leaning towards RULES since the syntax is considerably shorter and simpler then a corresponding trigger.

This depends most likely on how your updates will be accomplished, because

  "
  A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right."

- Postgres Documentation

So it seems that you find  rule simpler is quite convenient in the scenario where you make a large amount of changes to the data. Either should work, though.


Create a new column in a view and assign it a data type here is my query:

CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2 VARCHAR(10)
FROM VIEW1;

I get an error in the third line.

If I leave the query like this, without specifying the data type for Column2, the query works, but the default data type will be int, whereas I want to have VARCHAR.

CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2
FROM VIEW1;

Can you please help? Thank you!

Note: Only Column1 is in VIEW1. Column2 is NOT in VIEW1, it's a generated column with NULL values.

You'll want this.

CREATE VIEW VIEW2
AS SELECT [Column1],
cast(NULL as varchar(10)) AS column2
FROM VIEW1;


How can I swap two values from particular column in a table in Postgres? Consider a table test contains an attributes of id and name.

The values for that.

Id   |   Name
1    |   Raj
2    |   Kumar

From the above sample table, I know only the id, So through the Id I need to swap  the name, like given below,

Id   |   Name
1    |   Kumar
2    |   Raj

update the_table
   set name = case id
                 when 1 then (select name from the_table where id = 2)
                 when 2 then (select name from the_table where id = 1)
              end
where id in (1,2);

This assumes that id is unique (e.g. the primary key)


SQL Server - cannot drop idle job Currently, I am transitioning a server from a company to another.

They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs.

I'm trying to delete a bunch of SQL Server Agent jobs but even though they are disabled they are still on "IDLE" mode so it raises me the following error when trying to delete them:

  Drop failed for Job job_name.Subplan_1.  (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  The DELETE statement conflicted with the REFERENCE constraint FK_subplan_job_id. The conflict occurred in database "msdb", table dbo.sysmaintplan_subplans, column job_id.

  The statement has been terminated. (Microsoft SQL Server, Error: 547)

How can I remove the IDLE status from the job so the delete gets through?

You have maintenance plans. Try to follow these steps:

Find the maintenance plan name and id that you want to delete.
Write down the id of the one you want to delete.

 SELECT name, id FROM msdb.dbo.sysmaintplan_plans

--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table:

    DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query and delete the entry from subplans table:

    DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query to delete the entry from the plans table:

    DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ''

Now you can delete the jobs from Management Studio.


Interpolate values on Sorting in PSQL query In the database, I have a field as status which can have a value of

1, 2, 11, 12, 21, 22

1, 11, 21 Stands for processing

2, 12, 22 Stands for completed.

I am doing this query to get all data with sorting..

select se.*, cam.name, cam.exid, u.api_key, u.api_id from snapshot_extractors se
            left join cameras as cam on se.camera_id = cam.id
            left join users as u on cam.owner_id = u.id
            where lower(cam.name) like lower('%%') or lower(se.requestor) like lower('%%')
            order by se.status desc []

But when It sorts through status.. sorts works but in table formation where I am displaying the data, 22 and 2 means the same thing. but on sorting. either 2 will be up the list or 22 will be down or either way.

I am not getting any clue how I can make it work with sorting, so 22 and 2 can be at the top when I am sorting for completed ones.

You can sort using an expression:

order by case
        when se.status in (2, 12, 22) then 1
        when se.status in (1, 11, 21) then 2
        else 3 -- just in case
      end

This would sort rows with status "processing" before rows with "completed"


pgAdmin and PostgreSQL compatibility matrix Has anyone ever put together a version compatibility matrix for pgAdmin versions and PostgreSQL versions?

https://www.pgadmin.org/download/windows.php

https://www.postgresql.org/download/windows/

PostgreSQL versions 9.5 and above are incompatible with pgAdmin 3.

Attempting to connect gives the message:

  The server you are connecting to is not a version that is supported by this release of pgAdmin III. pgAdmin III may not function as expected. Supported server versions are 8.4 to 9.4.


"Show" Output Store Into Table Is there any way through which can I store my "show slave status" output into any permanent/temporary table. After storing output into table I will took some decisions against their error number.

Disclaimer: The below solution is probably unnecessarily complicated - the obvious solution to this would perhaps involve a cron job or maybe a database event running at some interval to execute SHOW SLAVE STATUS; and extracting the output into your table.

Nevertheless, here is what I think is an interesting solution which gives you instant access to the SHOW SLAVE STATUS output through a SELECT query. However, I've only been tested this with MariaDB (version 10.1.32), and it may or may not be portable to MySQL. It requires the CONNECT storage engine, and I'm not sure you can get that for MySQL.

First install the connect storage engine package in your OS. Then in the mysql client:

INSTALL SONAME 'ha_connect';
CREATE DATABASE IF NOT EXISTS `_`
  DEFAULT CHARACTER SET = 'utf8'
  DEFAULT COLLATE = 'utf8_general_ci';
USE _
CREATE OR REPLACE SERVER `_`
  FOREIGN DATA WRAPPER MYSQL OPTIONS (
    HOST 'localhost',
    USER 'root', PASSWORD 'your_password_here',
    PORT 3306,
    DATABASE '_'
  )
;
CREATE TABLE show_slave_status
  ENGINE = CONNECT
  TABLE_TYPE = MYSQL
  SRCDEF = 'SHOW SLAVE STATUS'
  CONNECTION = '_';

Now you can get all the values you usually get from SHOW SLAVE STATUS with a SELECT: SELECT * FROM _.show_slave_status;

Credits: I borrowed almost all of this from Federico Razzoli's materialize.sql.


Run script on multiple schemas on PostgreSQL DB I need to run a huge migration script on multiple schemas on PostgreSQL 9.3 server. I want output to be in a .txt format if this failed on any schema. Would you be able to help me on this?

Details:
All schemas are of similar structure and I have the list of schemas on which this script needs to be executed.

You could run the following bash script. It will expect the list of schemas in the schemas.txt file, and your script in myscript.sql. It will run the script on each schema (as a default schema) in mydatabase, and will produce, for each schema, a file named myscript-output-<the_schema_name>.txt.

For Unix, the script is:

cat schemas.txt | while read schema; do
  (
    echo "SET search_path TO $schema,public;"
    cat myscript.sql
  ) | psql -f - -o myscript-output-${schema}.txt mydatabase
done

For windows, the script is (use %%S instead of %S when used in a batch file):

for /F %S in (schemas.txt) do @(
  echo SET search_path TO %S,public;
  type myscript.sql
) | "C:\Program Files\PostgreSQL\9.3\bin\psql" -f - -o  myscript-output-%S.txt mydatabase


Database Mirroring protocol TCP ports used. One default, one dynamic? When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™

SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes
FROM sys.dm_exec_connections
WHERE local_net_address is not null;

Two local tcp ports show up for the Database mirroring protocol, 5022 & 63420

Server Name local_tcp_port  protocol_type       num_reads   num_writes
ServerName  5022            Database Mirroring  102942598   5
ServerName  63420           Database Mirroring  5           89655349

The 5022 port is expected, as this is the one configured as the mirroring endpoint.

The other one seems to be a dynamic port, why and for what is this one used?

Could it have to do with the fact that one is showing a high number of reads (5022) and the other one showing a high number of writes (63420).

  Build version: 13.0.5264.1

When an application establishes a TCP connection, it specifies a port for the inbound (receive) port, and uses a (somewhat) randomly selected port for outbound (source, or send).  The inbound port for AG replication is 5022 by default, so all replication traffic is sent TO port 5022.  Accordingly, you see the network reads occurring on port 5022, as SQL Server is listening on port 5022 and reading packets that are sent to that port.

The outbound port, which is selected randomly (more or less), is the port it is sent FROM.  Accordingly, you see the writes associated with the random port (63420 in your example), as SQL Server is writing data to this port to be sent on the connection.

See How are source ports determined... for more