Searching all database schemas - how to use wildcards? Using sqlworkBench I'm looking for column names. I'd like to use wild cards in my search using the below query. when I search for column "activated_at" it works but searching with wild card fails, can anyone help ?

SELECT
    table_schema,
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE column_name = 'activated_a*' ;

You can try this:

SELECT table_schema, table_name, column_name, data_type, ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'activated_a%' ;

When you want to use wildcard, you have to use LIKE instead of '='


Postgres ON DELETE Rule Not Working With WHERE Clause I'm trying to implement "Soft Deletes" given the following schema:

CREATE TABLE categories(
  id serial not null primary key,
  num integer,
  "name" text,
  deleted_at timestamp default null
);

CREATE OR REPLACE RULE delete_categories AS
  ON DELETE TO categories
  WHERE old.deleted_at IS NULL
  DO INSTEAD
    UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = old.id;

The expected behavior is that if I try to delete a record with a NULL deleted_at value, it will instead be set to the current timestamp.  If I try to delete a record with a non-NULL deleted_at value, it will be deleted normally.

Instead, running the below sequence returns no records, instead of a record with a timestamp in the deleted_at column:

insert into categories(num,name,deleted_at) values(9999,'Test Category',null);
delete from categories;
select * from categories;

It appears as though the RULE does not get triggered at all, and the record is simply deleted, whereas if I modify the RULE by commenting out the WHERE clause, the record is updated as expected but I am barred from being able to delete it fully:

CREATE OR REPLACE RULE delete_categories AS
  ON DELETE TO categories
  -- WHERE old.deleted_at IS NULL
  DO INSTEAD
    UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = old.id;

insert into categories(num,name,deleted_at) values(9999,'Test Category',null);
delete from categories;
select * from categories;

Results:

+----+------+---------------+----------------------------+
| id | num  |     name      |         deleted_at         |
+----+------+---------------+----------------------------+
|  3 | 9999 | Test Category | 2015-03-03 20:05:44.660208 |
+----+------+---------------+----------------------------+

The manual in Rules on INSERT, UPDATE, and DELETE) describes the INSTEAD mechanism for the context of your rule as:

  Qualification given and INSTEAD

  the query tree from the rule action with the rule qualification an
  the original query tree's qualification; and the original query
  tree with the negated rule qualification added

Overlooking the part emphasized above would be the cause of the unexpected result.
I believe that in your test case, the mentioned DELETE will be transformed by the rule into commands to the same effect as:

UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = categories.id AND deleted_at IS NULL;

DELETE from categories WHERE NOT old.deleted_at IS NULL;

The DELETE removes the row because the UPDATE before it has just set its deleted_at field to non-null.

Note also the categories.id = categories.id condition which is obviously not needed and comes from the misunderstanding that OLD does not reference a specific row like in a trigger, it's meant to be replaced by the table subject to the rule.

The main point to understand is that rules don't look themselves at the rows and so they don't generate a kind of IF-THEN-ELSE construct depending on the row contents. The WHERE clauses of the RULES (the qualifications) are not evaluated by the rule system, they are injected into the commands produced by the rule system and will be evaluated by the commands themselves.

See also What are PostgreSQL RULEs good for? and links within for some interesting insights on rules and why they're hard to use.


Solution to assigning unique values to rows with finite collaboration distance I have a table that can be created and populated with the following code:

CREATE TABLE dbo.Example(GroupKey int NOT NULL, RecordKey varchar(12) NOT NULL);
ALTER TABLE dbo.Example
    ADD CONSTRAINT iExample PRIMARY KEY CLUSTERED(GroupKey ASC, RecordKey ASC);
INSERT INTO dbo.Example(GroupKey, RecordKey)
VALUES (1, 'Archimedes'), (1, 'Newton'), (1, 'Euler'), (2, 'Euler'), (2, 'Gauss'),
       (3, 'Gauss'), (3, 'Poincaré'), (4, 'Ramanujan'), (5, 'Neumann'),
       (5, 'Grothendieck'), (6, 'Grothendieck'), (6, 'Tao');

For all rows that have a finite collaboration distance based on RecordKey with another row, I would like to assign a unique value—I don't care how or what data type the unique value is.

A correct result set that meets what I'm asking for can be generated with the following query:

SELECT 1 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(1, 2, 3)
UNION ALL
SELECT 2 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey = 4
UNION ALL
SELECT 3 AS SupergroupKey, GroupKey, RecordKey
FROM dbo.Example
WHERE GroupKey IN(5, 6)
ORDER BY SupergroupKey ASC, GroupKey ASC, RecordKey ASC;

To better aid what I'm asking, I'll explain why GroupKeys 1–3 have the same SupergroupKey:

GroupKey 1 contains the RecordKey Euler which in turn is contained in GroupKey 2; thus GroupKeys 1 and 2 must have the same SupergroupKey.
Because Gauss is contained in both GroupKeys 2 and 3, they too must have the same SupergroupKey. This leads to GroupKeys 1–3 having the same SupergroupKey.
Since GroupKeys 1–3 don't share any RecordKeys with the remaining GroupKeys, they are the only ones assigned a SupergroupKey value of 1.

I should add that the solution needs to be generic. The above table and result set was merely an example.

Addendum

I removed the requirement for the solution to be non-iterative. While I would prefer such a solution, I believe it's an unreasonable constraint. Unfortunately, I am unable to use any CLR-based solution; but if you want to include such a solution, feel free to. I will likely not accept it as an answer though.

The number of rows in my real table is as large as 5 million, but there are days when the number of rows will "only" be around ten thousand. On average there are 8 RecordKeys per GroupKey and 4 GroupKeys per RecordKey. I imagine that a solution will have exponential time complexity, but I am interested in a solution nonetheless.

This problem is about following links between items. This puts it in the realm of graphs and graph processing. Specifically, the whole dataset forms a graph and we are looking for components of that graph. This can be illustrated by a plot of the sample data from the question.

The question says we can follow GroupKey or RecordKey to find other rows that share that value. So we can treat both as vertexes in a graph. The question goes on to explain how  GroupKeys 1–3 have the same SupergroupKey. This can be seen as the cluster on the left joined by thin lines. The picture also shows the two other components (SupergroupKey) formed by the original data.

SQL Server has some graph processing ability built into T-SQL. At this time it is quite meagre, however, and not helpful with this problem. SQL Server also has the ability to call out to R and Python, and the rich & robust suite of packages available to them. One such is igraph. It is written for "fast handling of large graphs, with millions of vertices and edges(link)."

Using R and igraph I was able to process one million rows in 2 minutes 22 seconds in local testing1. This is how it compares against the current best solution:

Record Keys     Paul White  R
------------    ----------  --------
Per question    15ms        ~220ms
100             80ms        ~270ms
1,000           250ms       430ms
10,000          1.4s        1.7s
100,000         14s         14s
1M              2m29        2m22s
1M              n/a         1m40    process only, no display

The first column is the number of distinct RecordKey values. The number of rows
in the table will be 8 x this number.

When processing 1M rows, 1m40s were used to  load & process the graph, and to update the table. 42s were required to populate an SSMS result table with the output.

Observation of Task Manager while 1M rows were processed suggest about 3GB of working memory were required. This was available on this system without paging.

I can confirm Ypercube's assessment of the recursive CTE approach. With a few hundred record keys it consumed 100% of CPU and all available RAM. Eventually tempdb grew to over 80GB and the SPID crashed.

I used Paul's table with the SupergroupKey column so there's a fair comparison between the solutions.

For some reason R objected to the accent on Poincaré. Changing it to a plain "e" allowed it to run. I didn't investigate since it's not germane to the problem at hand. I'm sure there's a solution.

Here is the code

-- This captures the output from R so the base table can be updated.
drop table if exists #Results;

create table #Results
(
    Component   int         not NULL,
    Vertex      varchar(12) not NULL primary key
);

truncate table #Results;    -- facilitates re-execution

declare @Start time = sysdatetimeoffset();  -- for a 'total elapsed' calculation.

insert #Results(Component, Vertex)
exec sp_execute_external_script
    @language = N'R',
    @input_data_1 = N'select GroupKey, RecordKey from dbo.Example',
    @script = N'
library(igraph)
df.g <- graph.data.frame(d = InputDataSet, directed = FALSE)
cpts <- components(df.g, mode = c("weak"))
OutputDataSet <- data.frame(cpts$membership)
OutputDataSet$VertexName <- V(df.g)$name
';

-- Write SuperGroupKey to the base table, as other solutions do
update e
set
    SupergroupKey = r.Component
from dbo.Example as e
inner join #Results as r
    on r.Vertex = e.RecordKey;

-- Return all rows, as other solutions do
select
    e.SupergroupKey,
    e.GroupKey,
    e.RecordKey
from dbo.Example as e;

-- Calculate the elapsed
declare @End time = sysdatetimeoffset();
select Elapse_ms = DATEDIFF(MILLISECOND, @Start, @End);

This is what the R code does

@input_data_1 is how SQL Server transfers data from a table to R code and translates it to an R dataframe called InputDataSet.
library(igraph) imports the library into the R execution environment.
df.g <- graph.data.frame(d = InputDataSet, directed = FALSE) load the data into an igraph object. This is an undirected graph since we can follow links from group to record or record to group. InputDataSet is SQL Server's default name for the dataset sent to R.
cpts <- components(df.g, mode = c("weak")) process the graph to find discrete sub-graphs (components) and other measures.
OutputDataSet <- data.frame(cpts$membership) SQL Server expects a data frame back from R. Its default name is OutputDataSet. The components are stored in a vector called "membership". This statement translates the vector to a data frame.
OutputDataSet$VertexName <- V(df.g)$name V() is a vector of vertices in the graph - a list of GroupKeys and RecordKeys. This copies them into the ouput data frame, creating a new column called VertexName. This is the key used to match to the source table for updating SupergroupKey.

I'm not an R expert. Likely this could be optimised.

Test Data

The OP's data was used for validation. For scale tests I used the following script.

drop table if exists Records;
drop table if exists Groups;

create table Groups(GroupKey int NOT NULL primary key);
create table Records(RecordKey varchar(12) NOT NULL primary key);
go

set nocount on;

-- Set @RecordCount to the number of distinct RecordKey values desired.
-- The number of rows in dbo.Example will be 8 * @RecordCount.
declare @RecordCount    int             = 1000000;

-- @Multiplier was determined by experiment.
-- It gives the OP's "8 RecordKeys per GroupKey and 4 GroupKeys per RecordKey"
-- and allows for clashes of the chosen random values.
declare @Multiplier     numeric(4, 2)   = 2.7;

-- The number of groups required to reproduce the OP's distribution.
declare @GroupCount     int             = FLOOR(@RecordCount * @Multiplier);

-- This is a poor man's numbers table.
insert Groups(GroupKey)
select top(@GroupCount)
    ROW_NUMBER() over (order by (select NULL))
from sys.objects as a
cross join sys.objects as b
--cross join sys.objects as c  -- include if needed

declare @c int = 0
while @c < @RecordCount
begin
    -- Can't use a set-based method since RAND() gives the same value for all rows.
    -- There are better ways to do this, but it works well enough.
    -- RecordKeys will be 10 letters, a-z.
    insert Records(RecordKey)
    select
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND())) +
        CHAR(97 + (26*RAND()));

    set @c += 1;
end

-- Process each RecordKey in alphabetical order.
-- For each choose 8 GroupKeys to pair with it.
declare @RecordKey varchar(12) = '';
declare @Groups table (GroupKey int not null);

truncate table dbo.Example;

select top(1) @RecordKey = RecordKey
from Records
where RecordKey > @RecordKey
order by RecordKey;

while @@ROWCOUNT > 0
begin
    print @Recordkey;

    delete @Groups;

    insert @Groups(GroupKey)
    select distinct C
    from
    (
        -- Hard-code * from OP's statistics
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
        union all
        select FLOOR(RAND() * @GroupCount)
    ) as T(C);

    insert dbo.Example(GroupKey, RecordKey)
    select
        GroupKey, @RecordKey
    from @Groups;

    select top(1) @RecordKey = RecordKey
    from Records
    where RecordKey > @RecordKey
    order by RecordKey;
end

-- Rebuild the indexes to have a consistent environment
alter index iExample on dbo.Example rebuild partition = all
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

-- Check what we ended up with:
select COUNT(*) from dbo.Example;  -- Should be @RecordCount * 8
                                   -- Often a little less due to random clashes
select
    ByGroup = AVG(C)
from
(
    select CONVERT(float, COUNT(1) over(partition by GroupKey))
    from dbo.Example
) as T(C);

select
    ByRecord = AVG(C)
from
(
    select CONVERT(float, COUNT(1) over(partition by RecordKey))
    from dbo.Example
) as T(C);

I've just now realised I've gotten the ratios the wrong way around from the OP's definition. I don't believe this will affect the timings. Records & Groups are symmetrical to this process. To the algorithm they're all just nodes in a graph.

In testing the data invariably formed a single component. I believe this is due to the uniform distribution of the data. If instead of the static 1:8 ratio hard-coded into the generation routine I had allowed the ratio to vary there would more likely have been further components.

1 Machine spec: Microsoft SQL Server 2017 (RTM-CU12), Developer Edition (64-bit), Windows 10 Home. 16GB RAM, SSD, 4 core hyperthreaded i7, 2.8GHz nominal. The tests were the only items running at the time, other than normal system activity (about 4% CPU).


Hide zero hours and minutes with NLS_DATE_FORMAT I'm currently using date format:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI';

The (cosmetic) problem is that this always displays time even if it's zero, for example: "27.03.2014 00:00". Is it possible to hide 00:00 with NLS_DATE_FORMAT, but display the time when it's not zero?

I realize this is not a good idea for any kind of automatic processing but this is only for interactive use. I know I can format individual columns in the query but I'm interested in more general solution.

I did check the format model documentation but I didn't find any relevant parameters so maybe it's not possible.

No, it can not be done, because 00:00 is a valid time. That you use it to represent "no time available" in your application is your own decision and you have to take care of the consequences yourself.

You could store date and time in separate columns and show only the date if the time is NULL.


Character Set Database Migration This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but I'd like to get a little bit more information about the process. Our current character set is WE8ISO8859P1 and uses BYTE character semantics. However, we are migrating to AL32UTF8. A new database was created containing the AL32UTF8 character set using BYTE semantics. The DBA ran a script on some tables that checked to see if there would be any issues (I believe it was CSALTER). After running the script, it said that some of the rows in our table would be truncated. That table contains a VARCHAR2(BYTE) column that's set to 4000 BYTES. The rows that resulted with potential issues all contain special characters. My question is, how can we export/import the tables in our database without having information truncated? Why is it saying that it would be truncated? My first thought was that since we changed the character set, it may be storing some of the characters from the old database table different than when it's stored into the new database with the character set using up more storage.

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.

By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:

SQL>  select length(convert(chr(128),'AL32UTF8','WE8ISO8859P1')) from dual
  2  /
LENGTH(CONVERT(CHR(128),'AL32UTF8','WE8ISO8859P1'))
---------------------------------------------------
                                                  2
SQL>

To list all characters that will be affected by the change, you can use the following query:

with n as
(
select level as c from dual
connect by level <= 255
)
select c as "WE8ISO8859P1 value",
       '"'||chr(c)||'"' as Character,
       length(convert(chr(c),'AL32UTF8','WE8ISO8859P1')) as "New length"
from n
where length(convert(chr(c),'AL32UTF8','WE8ISO8859P1'))>1;

Unfortunately the maximum length a CHAR or VARCHAR string can be in Oracle is 4000 bytes. The only option available to you if characterset conversion pushes you over this limit is to convert the columns to use the CLOB datatype, but we warned - CLOBs are difficult to deal with and can present challenges.


De-Cluster SQL Server 2012 Is there any way to 'de-cluster' an instance of SQL Server 2012 that was installed using the 'New SQL Server Cluster Installation' option? We are having issues with the failover portion of clustering services and since we did not implement the cluster (and really there is no need) we would like to turn this into a stand-alone installation. Removing the Windows Clustering portion just took SQL down with it. All help and insight is appreciated, thanks!

You need to do this through the installer. Choose remove node from cluster in the SQL Server installer.

http://msdn.microsoft.com/en-us/library/ms191545.aspx


Do indexes on primary keys default to fillfactor of 90%, and should they be set to 100%? Does Postgres automatically set the fillfactor to 100 instead of 90 when an index is made on a primary key column? If not, is there any reason not to manually set it to 100%?

https://www.postgresql.org/docs/current/static/sql-createindex.html

  B-trees use a default fillfactor of 90, but any integer value from 10 to 100 can be selected.

Why would it matter that the fillfactor is on a primary key? The index works the same way. If you UPDATE an indexed row to a point to a new version of the row -- that is an UPDATE that wasn't heap-only, or if you add a row you have to potentially split the page the index is stored on to make room for the addition.

The point at which you have to do that is when the index's page is full. The question is after the pages are properly sized for the index, how much extra-space on those pages should be left for future operations? If the fillfactor was 100, every operation that wrote to the page would result in the page split. That would be super-slow.

So what fillfactor 90 does is leave a 10% gap. That's 10% more you can grow before you have to pay that price again. Only if you know you won't have to grow the index again would you want to set it to fillfactor 100 (and then REINDEX and shrink down all the pages).


I want to Move some Mysql table to InnoDB but i don't know what is the best my.cnf conf This is my server specification

my.cnf:

[mysqld]

innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=50000
default-storage-engine=MyISAM
myisam_sort_buffer_size=256M
table_open_cache = 600
thread_cache_size = 4
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

join_buffer_size = 256k
tmp_table_size = 20M
max_connections = 600

slow-query-log = 1
slow-query-log-file = /output/mysql-slow.log
long_query_time = 1
datadir=/disk3/mysql

Server Ram

             total       used       free     shared    buffers     cached
Mem:           62G        61G       1.5G       1.8G       2.0G        53G
-/+ buffers/cache:       5.6G        57G
Swap:         1.9G       230M       1.7G

CPU

model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz × 2
cpu MHz         : 2000.022
cache size      : 15360 KB

I have a very big MyIsam Table , each user has status record in it, I select insert join to it. Here is the table :

CREATE TABLE `status_userid` (
             `id` int(10) NOT NULL AUTO_INCREMENT,
             `md5id` char(32) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `ptype` char(1) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `media_url` varchar(100) COLLATE utf8_swedish_ci NOT NULL,
             `media_photo` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
             `user_id` varchar(50) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `user_name` varchar(100) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
             `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
             `reason` text COLLATE utf8_swedish_ci,
             PRIMARY KEY (`id`),
             KEY `md5id_mediaid` (`md5id`,`media_id`),
             KEY `user_id_md5id` (`user_id`,`md5id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

from mysql.slow.log:

Count: 1165  Time=11.17s (13008s)  Lock=0.00s (0s)  Rows=548.3 (638790), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  s1.id as order_by,
  s1.media_id,
  s1.media_photo,
  s1.user_id,
  s1.user_name,
  s1.date
  FROM `status_427` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_427`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

Count: 2247  Time=8.09s (18172s)  Lock=0.00s (1s)  Rows=1.0 (2247), myinstar_ranker[myinstar_ranker]@localhost
  SELECT
  count(N) as counter
  FROM `status_421` s1
  LEFT JOIN
  (SELECT md5id, user_id, ptype FROM `status_421`  WHERE md5id = 'S') s2
  ON
  s1.user_id = s2.user_id AND s2.ptype = 'S'
  WHERE
  s1.md5id = 'S'
  AND s2.md5id IS NULL
  AND s1.date  < NOW() - INTERVAL N DAY
  AND s1.ptype = 'S'

I manage to create separate table for each user so I don't end with a huge table if it damaged I lost everything. but this table is very big one, 8M record and counting, that's is why I decided to split it.

Mysql directory on SSD drive.

I have a huge mysql using, and the server top is:

Tasks: 781 total,   5 running, 775 sleeping,   1 stopped,   0 zombie
Cpu(s): 41.1%us, 10.1%sy,  0.0%ni, 48.5%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  65784692k total, 64190512k used,  1594180k free,  2141220k buffers
Swap:  1999868k total,   236436k used,  1763432k free, 56162120k cached

    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 459105 mysql     20   0 12.4g 286m 6588 S 564.2  0.4 137:35.08 mysqld

So, does innoDb may help to reduce mysql Load?
What is the best inndoDb my.cnf setting, I was told innoDb is very sensitive and can be damaged so fast, and even more it create a huge log file.

I am not Mysql expert, I know php and some of mysql, but not expert in tuning it.

What are the best setting I can use to reduce server load?

innodb_buffer_pool_size is the most important setting for InnoDB.  See http://mysql.rjweb.org/doc.php/memory
Tips on converting from MyISAM:  http://mysql.rjweb.org/doc.php/myisam2innodb
Don't use utf8 for ascii strings.  md5id?
Change user_id_md5id to INDEX(user_id, md5id, ptype) for the LEFT JOIN ( SELECT ... ).
INDEX(md5id, ptype, date), with date last, for the outer query.
Rethink having lots of tables named status_nnn; there may be better ways to design the schema.
max_connections = 600 and 5 running, 775 sleeping -- It may be better to throttle the number of clients, and not let so many get to MySQL.
How big is the table?  (rows or GB)


How to rebind a package inside a Stored Procedure (DB2) I have a few Stored Procedures for my application (DB2 V 9.7.3). I need to rebind the packages corresponding to the Stored Procedures. I wish to get having them recompiled inside a Stored Procedure by retrieving the package names via a Cursor and rebind the packages one by one.

The command to do is

REBIND PACKAGE <schema>.<pkgName>

To execute this command, I tried the following options but both of them failed.

EXECUTE IMMEDIATE <stmt>;

CALL SYSPROC.ADMIN_CMD(<stmt>);

Is there a way to get this accomplished? Any help would be highly appreciated.

There is a way to rebind the packages via the following routine

db2 "CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'SCHEMA1.PROC1', 'ANY')"

P -> stands for Stored Procedure
SCHEMA1.PROC1 -> stored procedure name with the schema
ANY -> an optional input argument supported for backward
compatability. You can use ANY or CONSERVATIVE.

Please refer these links for more information.

http://www-01.ibm.com/support/docview.wss?uid=swg21250633

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0009863.html

Note: Explicitly rebinding the associated package does not revalidate the routine. Revalidate an invalid routine by using automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Routine revalidation automatically rebinds the dependent package.


PostgreSQL 9.6 - Log integrity I have a PG 9.6 DB and I need to add some auditing and after googling, I have found pgaudit. Before trying to install and playing a bit with it, I'd like to make sure it complies with all my requirements.

The first concern I have regards data integrity: from my understanding, the extension stores the information in a simple log file without any data integrity check, so If I open the log and I manually delete something nobody will never notice it. Am I correct?

Does PG come with any log integrity mechanism?

Thanks

For your concern, i would suggest, you make the generated log files read only so that nobody other than the owner can change its contents. Anyway if contents of any file is changed, you can check its last modification time by using command stat filename.

Till now postgresql doesn't have any reliable auditing tools, and by looking at the pgaudit link, it just gives you some extra information from what is present in database logs, and will create a logfile possibly bigger than the database log. It could result in space constraints on your server.


Good example of MDX vs SQL for analytical queries Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results.

Wikipedia says:

  While it is possible to translate some of these into traditional SQL,
  it would frequently require the synthesis of clumsy SQL expressions
  even for very simple MDX expressions.

But there is neither a citation nor example. I am fully aware the underlying data must be organized differently, and OLAP will require more processing and storage per insert. (My proposal is to move from an Oracle RDBMS to Apache Kylin + Hadoop)

Context: I am trying to convince my company that we should be querying an OLAP database instead of an OLTP database. Most SIEM queries make heavy use of group-by, sort, and aggregation. Besides the performance boost, I think OLAP (MDX) queries would be more concise and easier to read/write than the equivalent OLTP SQL. A concrete example would drive the point home, but I am not an expert at SQL, much less MDX...

If it helps, here is a sample SIEM-related SQL query for firewall events that happened in the past week:

SELECT   'Seoul Average' AS term,
         Substr(To_char(idate, 'HH24:MI'), 0, 4)
                  || '0'        AS event_time ,
         Round(Avg(tot_accept)) AS cnt
FROM     (
                SELECT                     *
                FROM   st_event_100_#yyyymm-1m#
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query#
                UNION ALL
                SELECT *
                FROM   st_event_100_#yyyymm#
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query# ) pm
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'
UNION ALL
SELECT   'today' AS term ,
         substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'        AS event_time ,
         round(avg(tot_accept)) AS cnt
FROM     st_event_100_#yyyymm# cm
WHERE    idate >= trunc(sysdate) #stat_monitor_group_query#
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
                  || '0'
ORDER BY term DESC,
         event_time ASC

MDX and SQL are in no way the same, and often not even comparable, as they are querying multidimensional and relational databases respectively. You cannot query your existing relational database with MDX.

The main advantage of using a multidimensional model and using MDX to query it is that you are querying pre-aggregated data and that MDX is optimized to query in a statistical way rather than a relational way. You no longer query rows and tables to produce a flat result set but you are using tuples and sets to slice and aggregate a multidimensional cube.

Think of it like this: if you use a SQL query to get the total sales amount for a particular item group you would need to write a query that sums up all invoice lines for all items in the item group. If you are using a cube and have aggregations on the item group level the result is calculated during processing and the aggregations are stored for each item group, making queries instantaneous.

Multidimensional and MDX is an entirely different concept from relational set-based SQL.

Your example might become a lot simpler because you would be doing the transformations such as the date parsing during your data load process and your last month comparison could be a calculated measure. Your seoul average and today could be calculated members

If your cubes are well designed for your requirements I believe you could be slicing and dicing your example's data set without even needing to write queries but do it in a pivottable or another analysis tool.

Then again there is no "just rewriting SQL in MDX". It requires a fair bit of knowledge to do it right and a different mindset. Think venn-diagrams instead of result sets.

To provide you with an example using the adventureworks database, imagine the requirement to list the number of sales orders by customer in the category bikes.

If you did that using SQL you would need to write a query that counts the number of sales orders containing a line with a product that happens to be of the category bikes and join that to the customers table, so that would become a fairly complex query.

-- need distinct count, we're counting orders, not order lines
SELECT count(DISTINCT soh.salesorderid)
    ,pers.FirstName + ' ' + pers.LastName
FROM sales.SalesOrderDetail sod
-- we need product details to get to the category
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
-- but we need to pass via subcategories
INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
-- we finally get to the category
INNER JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
-- we also need the headers because that's where the customer is stored
INNER JOIN sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
-- finally the customer, but we don't have his name here
INNER JOIN sales.Customer c ON soh.CustomerID = c.CustomerID
-- customers
INNER JOIN Person.Person pers ON c.PersonID = pers.BusinessEntityID
-- filter on bikes
WHERE pc.Name = 'bikes'
-- but the customers table doesn't contain the concatenated name
GROUP BY pers.FirstName + ' ' + pers.LastName;

In MDX (provided your cube is well designed for this requirement) you could just write because the logic and complexity has moved elsewhere:

SELECT [Measures].[Internet Order Count] ON COLUMNS,
[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Category].[Bikes]


Linked Server - Map Windows Group to remote login I have a MySQL server that I need to set up as a linked server in SQL Server 2014.

I need a login from the SQL Server (which is a Windows group) to impersonate a username at the MySQL end.

I can see from Googling that this cannot be done.

I can create a new Windows login for every user in the Windows Group and map them, but was wondering if there is a slicker way of doing this?

I am not an admin of the MySQL server so cannot make any config changes there.

Further reading here (no affiliation): https://searchsqlserver.techtarget.com/tip/Managing-linked-server-security-in-SQL-Server

A linked server offers a few methods for connecting to the remote system.  Since MySQL as a remote system does not understand windows authentication or participate in Kerberos you are limited to the following:

Login mapping - as you said, each user in the windows group would need to be added to the linked server security tab, with a login on the MySQL side.  There is no reason that they cannot "share" the same login on the MySQL side, but each user would need to be added to the linked server on the SQL Side.
Be Made Using this security context - You can provide a single username and password here, such that every login on SQL Server would use it when using the Linked Server.

The only one that offers fine grained security control is the Login Mapping method.   Otherwise, any user who has access to SQL would have the access to MySQL that is granted by the Linked Server.


Mysql - latin1 (with latin2 content) to utf8 I have a database (ubbthreads) encoded in latin1 with content from latin2 (polish characters).

Since latin2 is compatible with latin1 it looks fine on the website, however I cannot convert it in any way to utf8 (want to import the data to nodebb).

It also doesn't render characters correctly in console mysql as well as in mysql workbench.

I tried numerous of ways to do the conversion and all of this failed.

My last try was to convert mysqldump file first from latin1 to latin2, and I planned to do next conversion from latin2 tu utf8.

However, iconv breaks in first polish character attempt with
illegal input sequence at position 11392

I need some tips how to do it in a correct way.

You can try something like:

INSERT INTO original_new()
SELECT CONVERT(CAST(CONVERT(`field` USING latin2) AS BINARY) USING utf8)
FROM original


Is there any way to use "like" operator during oracle table partitioning I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me.

In my table one column is URI, and for that column I want to use the like operator during partition so that it can help during insertion, because it contains different type of uris such as model://a/b or list://a/b.

I want something like:

CREATE TABLE table_name (
    URI varchar2(20);
)
PARTITION BY LIST ( URI )
      (PARTITION part_mod VALUES (like 'model://%'),
       PARTITION part_list VALUES (like 'list://%'),
       PARTITION part_def VALUES (default)
       );

I tried using Virtual Column-Based Partitioning but didn't get any success. Partitions were created, but during insertion no data is going into corresponding partitions; everything is going into part_def.

So is it possible to use the like operator to partition a table in oracle database?

Virtual column partitioning:

create table table_name (
    uri varchar2(20),
    part as (
      case
        when uri like 'model://%' then 'mod'
        when uri like 'list://%' then 'list'
        else 'def'
      end
    )
)
partition by list (part) (
  partition part_mod values ('mod'),
  partition part_list values ('list'),
  partition part_def values ('def')
);

Seems to work:

insert into table_name (uri) values ('model://abc');
insert into table_name (uri) values ('list://abc');
insert into table_name (uri) values ('test://abc');

select * from table_name partition (part_mod);
select * from table_name partition (part_list);
select * from table_name partition (part_def);

Inserted rows are returned from correct partitions.


Turn off auto qualification of table names when creating a view Problem Definition
I am trying to modified a shared development database, for a dev team to use for application development/testing.
Most of the collective work (table names, views, etc) are stored in the public schema, but I have set up schemas for each user to use as scratch space. However, the real goal would be for a user to use the objects in their schema if they exist and then fall back on the other objects, like how a search_path is used today.
Example
This would probably be better described with an example.  Let's say there's a database for an automotive repair application that a team is working on that has some tables and a view:
public.automobiles
public.parts
public.inventory
public.mechanics
public.schedule
public.v_repairs  -- view that joins fields from all tables above

This is working great, but let's say a developer (e.g., Sally) wants to test a new feature out, with her own dataset to check for visual feedback, or threshold testing.  She creates a table in her own schema, sally.schedule.  Because the default search_path is something like "$user",public, any simple queries she creates, would first check her schema before public.  For example:
SELECT * FROM schedule LEFT JOIN mechanics USING(mechanic_id);

This would use public.mechanics and sally.schedule, when sally is connected to the database.  That's exactly the intended use, but when saving a view, it fully qualifies the table name by inserting the schema.  So if the same query above was created as a view in the public schema it would look like:
SELECT * FROM public.schedule LEFT JOIN public.mechanics USING(mechanic_id);

The magic of search_path is negated.  When Sally connects to the database to  call the view (SELECT * FROM v_mechanics_schedule), it ignores the sally.schedule table she created, and only uses the public one.
Is there a way to not have Postgres store the schema name for table/view objects when saving a view?

Note: this is something new I was looking into, but something I've never really had the need for, since developers can typically clone applications, replicate databases, and work within their own sandbox environment. No need for some clever collaborative schema setup

No. There is no way. I think there is a slight misconception of how things work.
Unlike functions, which store the function body as string (late binding!), a view parses the query and does not store the original text at all. All identifiers are resolved according to the current search_path and only their internal OIDs are saved (early binding!). That's also why SELECT * always resolves to the list of columns at the point in time when the VIEW was created.
What you see in pgAdmin or other clients when looking at the definition of a VIEW is a re-engineered SQL string. public.schedule will always be public.schedule and sally.schedule will always be sally.schedule (unless you rename table or schema). The (schema-qualified) names that are displayed are chosen such that the identifier is unambiguous with regard to the current search path.
This also shows when you change the names of columns, tables or schemas. The view keeps just working, because it does not depend on these attributions. The display of the definition is adapted dynamically.
Possible solution
If you want late binding you could use a function instead of a view and default to the current search path (one could SET the search path for the scope of a function to avoid that effect precisely.)
So instead of:
CREATE VIEW v_mechanics_schedule AS
SELECT * FROM schedule s  -- use aliases for short qualified column names in display
LEFT     JOIN mechanics m USING (mechanic_id);

Without schema-qualification, table names are resolved according to the current search_path at creation time.
You could:
CREATE FUNCTION f_mechanics_schedule()
  RETURNS TABLE (...) AS  -- you have to spell out columns
$func$
SELECT * FROM schedule s  -- this is typically error prone
LEFT     JOIN mechanics m USING(mechanic_id);
$func$  LANGUAGE SQL

Now, table names are resolved according to the search_path at execution time. Note that the same does not apply to the return type ( RETURNS TABLE (...)), which is determined at creation time. So this trick only works for compatible tables - the query has to return the same list of columns, only data types and the number of columns matter, column names are ignored (only the names in the definition of the return type are visible outside the function).
That's also why SELECT * to return values in the function body is typically unreliable. If you change the definition of underlying tables, the function breaks: The defined return type remains the same, but SELECT * resolves to a different column list ...
OTOH, if you declare a function as RETURNS SETOF some_table (different syntax variant), a functional dependency is registered and the return type of the function is bound to the table definition, so SELECT * FROM some_table would make more sense.
This is also the reason why it is unsafe to have SECURITY DEFINER functions without fixing the search_path: Any user with the TEMP privilege can create a temp table that hides other tables because pg_temp comes first in the search path by default ...
Related:

Permission for sequence in another schema

You really need to understand the underlying mechanisms to play with this.


How Do I Execute SSIS Catalog Packages As A Different User With TSQL? We've been moving application packages from the older methods of storage and execution into the SSIS Catalog.  We're required to use an enterprise scheduling system instead of the SQL Server Agent to execute batch jobs and SSIS packages.  Basically, the scheduler opens a command line on the server and executes Windows commands.  In our case, we'd be using SQLCMD to execute a stored procedure like the one below.

Here's what I need to be able to do:

CREATE PROCEDURE usp_Execute_Packages @Package_name NVARCHAR(260)
WITH EXECUTE AS 'Domain\User'
AS
DECLARE @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=@Package_name, @execution_id=@execution_id OUTPUT, @folder_name=N'A Folder Name', @project_name=N'Test Deployment'

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

But, this doesn't seem to work; I get an error telling me that I can't use a SQL Server account to start an execution.  I assume it's having an issue with the  EXECUTE AS statement.  Any ideas how to start the package execution as a different user without using SQL Server Agent and proxy accounts?

If you create the stored procedure in SSISDB, then add the EXECUTE AS statement within the stored procedure and you can execute the package as the necessary login, which is what it looks like you're aiming to do.  As @sepupic noted, adding EXECUTE AS in the header of the SP only applies to database users.

USE SSISDB;
GO

CREATE PROCEDURE usp_Execute_Packages

AS BEGIN

EXECUTE AS LOGIN = 'Domain\YourLogin'

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'YourPackage.dtsx',
@execution_id=@execution_id OUTPUT, @folder_name=N'Your Folder', @project_name=N'YourProject',
@use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50,
@parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id

END


SQL Server 2012 - Can't detach database on mirror server I'm running SQL Server 2012 in a mirror configuration and I need to move the mirror's transaction files.

I deleted the mirror setup on the primary but I can't detach the databases on the mirror, the option is grayed out.

I used activity monitor and the database files are NOT being used so I should be able to detach the databases. Does anybody have any clues as to what the hold up is?

Thank you.

To remove database mirroring

Connect to the Database Engine of either mirroring partner.
From the Standard bar, click New Query.
Issue the following Transact-SQL statement:

ALTER DATABASE database_name SET PARTNER OFF

where database_name is the mirrored database whose session you want to remove.

The following example removes database mirroring from the AdventureWorks2012 sample database.

ALTER DATABASE AdventureWorks2012 SET PARTNER OFF;


MySQL query returns different result on second execution I'm not too familiar with MySQL variables but I'm trying to avoid redundant sub selects while calculating these results. The first execution only returns 1 of the correct calculations. The second execution returns all the correct values.

SELECT
@discount_code := '*******' AS discount_code,
@sales_revenue := (
    SELECT
        CAST(SUM(total_price) AS UNSIGNED)
    FROM
        shopify_orders
    WHERE
        discount_code = @discount_code
) AS sales_revenue,
@rsp_float := (
    SELECT
        revenue_share_percentage / 100
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS rsp_float,
@marketing_spend := (
    SELECT
        fixed_media_cost + @revenue_share
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS marketing_spend,
@roi_dollars := (
    @sales_revenue - @marketing_spend
) AS roi_dollars,
@revenue_share := (@sales_revenue * @rsp_float) AS revenue_share,
@roi := CAST(
    (@roi_dollars /@marketing_spend) AS DECIMAL (5, 2)
) AS roi,
@mroi := CAST(
    (
        @sales_revenue / @marketing_spend
    ) AS DECIMAL (5, 2)
) AS mroi;

First execution:

Second execution:

The order of columns is important, if you are going to use mysql variables this way. You are using @revenue_share before it is calculated. So, the first time the query runs, NULL is used for the calculation of @marketing_spend. The second time the query runs, the variable has a value saved from the 1st run.

Additionally, the two subqueries that have FROM kpi_data can be combined in one. And you don't really need to use variables. You can get the same results using derived tables and one parameter only (@discount_code), used three times:

SELECT
    @discount_code AS discount_code,
    sales_revenue,
    rsp_float,
    marketing_spend
    sales_revenue - marketing_spend AS roi_dollars,
    revenue_share,
    CAST( ((sales_revenue - marketing_spend) / marketing_spend) AS DECIMAL (5, 2) )
        AS roi,
    CAST( (sales_revenue / marketing_spend) AS DECIMAL (5, 2) )
        AS mroi
FROM
    (   SELECT
            sr.sales_revenue,
            kd.revenue_share_percentage / 100  AS rsp_float,
            kd.fixed_media_cost,
            sr.sales_revenue * kd.rsp_float AS revenue_share,
            kd.fixed_media_cost + (sr.sales_revenue * kd.rsp_float)
                AS marketing_spend
        FROM
            (   SELECT
                    CAST(SUM(total_price) AS UNSIGNED) AS sales_revenue
                FROM
                    shopify_orders
                WHERE
                    discount_code = @discount_code
            ) AS sr
          CROSS JOIN
            kpi_data AS kd
        WHERE
            kd.CODE = @discount_code
    ) AS t ;


UPDATE with Oracle MERGE statement I have following MERGE statement. Table table_A contains list of ID (cli_id) and column fb_flag. The query inside update the column fb_flag in table_A when cli_id is matching. Which works.
However when I want insert something else when the IDs are not matching I have difficulties.
See my two attempts in comments.

MERGE INTO tab_A chi
USING (SELECT    cli_id,
                 'TRUE' AS free_book_b
      FROM       tab_freebook d) fb
ON (chi.cli_id = fb.cli_id)
WHEN MATCHED THEN
  UPDATE SET chi.fb_flag = fb.free_book_b;
--first attempt
/* WHEN NOT MATCHED THEN
     UPDATE SET chi.fb_flag = 'FALSE'; */
--second attempt
/* WHEN NOT MATCHED THEN
     INSERT (chi.cli_id)
     VALUES ('FALSE')
     WHERE chi.cli_id IS NULL; */

Thank you for any advice.

EDIT:
Expected result:

tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     NULL    |
|   2    |     NULL    |
|   3    |     NULL    |
|   4    |     NULL    |

tab_freebook

| cli_id | free_book_b |
------------------------
|   23   |     TRUE    |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   6    |     TRUE    |

What I need have in tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     FALSE   |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   4    |     FALSE   |

I have found temporary solution - when I am creating tabA, I fill it with 'FALSE' and then the merge update only the columns where the condition is met.
Which gives me expected result, but it's not really nice and clean as with just merge.

Try this ...

  create table taba ( cli_id  number, fb_flag varchar2(10) );
  insert into taba values ( 1, null );
  insert into taba values ( 2, null );
  insert into taba values ( 3, null );
  insert into taba values ( 4, null );

  create table tab_freebook ( cli_id  number, free_book_b varchar2(10) );
  insert into tab_freebook values ( 23, 'true' );
  insert into tab_freebook values ( 2, 'true' );
  insert into tab_freebook values ( 3, 'true' );
  insert into tab_freebook values ( 6, 'true' );

  commit;

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1
           2
           3
           4

  select * from tab_freebook;

      CLI_ID FREE_BOOK_
  ---------- ----------
           2 true
           3 true
           6 true
          23 true

  select a.cli_id, a.fb_flag  old_val,
         nvl(tf.free_book_b, 'FALSE') new_val
    from taba  a
        LEFT OUTER JOIN tab_freebook tf
           ON a.cli_id = tf.cli_id
  /

      CLI_ID OLD_VAL    NEW_VAL
  ---------- ---------- ----------
           2            true
           3            true
           4            FALSE
           1            FALSE

  SQL>

  MERGE INTO taba  base
     using (
              select a.cli_id, a.fb_flag  old_val,
               nvl(tf.free_book_b, 'FALSE') new_val
          from taba  a
              LEFT OUTER JOIN tab_freebook tf
                 ON a.cli_id = tf.cli_id
           )  new
     ON ( new.cli_id = base.cli_id )
     WHEN MATCHED THEN UPDATE
           SET base.fb_flag = new.new_val
  /

  4 rows merged.

  select * from taba;

      CLI_ID FB_FLAG
  ---------- ----------
           1 FALSE
           2 true
           3 true
           4 FALSE

  SQL>

[edit] fixed typo - somehow my initial test missed your test record "23" .. fixed, same result ;) [/edit]

Basically, start by writing a SELECT query ONLY that returns the end result set you want.
Then use that in your USING clause .. and viola ... "magic" ;)

although that said, you could probably avoid the MERGE altogether and just use an UPDATE  ;)  (that said, I actually use MERGE now more often than a straight UPDATE when the logic starts to get a little complex ... that is, for simple UPDATES, I use UPDATE, for complex UPDATEs, I use MERGE ;)  I just find the syntax easier ... shrug )


SP2-0734: unknown command beginning "sys_contex..." - rest of line ignored I Googled how to get the username of the current user and was told to use:
SQL> sys_context('userenv', 'session_user')

But when I type that command in, I get the error:

SP2-0734: unknown command beginning "sys_contex..." - rest of line ignored

What am I missing? The Oracle version is Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Use dual table part of select
SQL> SELECT sys_context('userenv', 'session_user') FROM dual;

Documentation:
SYS_CONTEXT


MAMP PRO not showing all MAMP database I was earlier using MAMP and Now switched to MAMP PRO. I used to use MAMP mysql as root default user and having lots off database as that user. If i start MAMP and go to terminal with mysql command and do show databases; then it show me all databases. But if i start MAMP PRO and goto terminal and do show databases; then it show me only 4 databases mysql, information_schema, performance_schema and one db which is common for both MAMP and MAMP PRO. How can i use those existing DB on MAMP under MAMP PRO.

I've never used MAMP or even heard of it until I read this question, but if both MySQL installations are the same base version of MySQL you can probably fix this by copying the databases from one installation's data directory to the other.

First, run the following in MySQL in each MySQL installation:

SHOW GLOBAL VARIABLES LIKE 'datadir';

That will tell you where the databases are for each installation.

Next, stop both MySQL installations. This is important because if they are running, you won't be able to make a consistent copy of the data.

Next, make a backup of the MAMP Pro MySQL installation's datadir. You should be able to do this by running the following in a shell:

cp -a <MAMP Pro datadir> <new backup directory>

Now copy the old databases to the new:

cp -a <MAMP datadir> <MAMP Pro datadir>

Finally, start the MAMP Pro instance and confirm whether the databases are present.

You could also do a backup and restore with mysqldump, but a copy is probably simpler especially since this is only for development.


mysql slave replication doesn't work due to error 1236 My mysql master server has crashed due to storage issue. The issue has been fixed and the master is up and running. However, my slave has stopped to be replicated due to the error:
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Here is my slave status:

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.x.x.x
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000321
          Read_Master_Log_Pos: 3962375
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000321
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3962375
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:

And here is my master status:

mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000323
        Position: 103145634
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

I can see that the specific binary log (mysql-bin.000321) doesn't exist on the master. The the log file itself exist on the server, but ain't shown by SHOW BINARY LOGS;.

mysql> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000314 | 1073742675 |
| mysql-bin.000315 | 1073742958 |
| mysql-bin.000316 | 1073742098 |
| mysql-bin.000317 |  631008456 |
| mysql-bin.000318 |   18007666 |
| mysql-bin.000319 |        125 |
| mysql-bin.000320 |  327413876 |
| mysql-bin.000322 |        125 |
| mysql-bin.000323 |  109150417 |
+------------------+------------+

Please advise.

Sounds like this sequence happened:

The slave went down, but you did not notice it.
A few days(?) went by.
The binlogs (on the Master) were automatically purged (either by a script you use, or by expire_logs_days)
Now you noticed the Slave was stopped, but it is too late.

If this is not the only slave, stop another slave and clone it to rebuild this one.

If it is the only slave, you are stuck with rebuilding the slave from the master.  Not fun.

But... You said the Master died and was repaired??  Well, look at the timeframes, and other things that I suggest above.  How many days (#2); how fast is the purging (#3), etc?


Incurs_seek_penalty column of sys.dm_os_volume_stats A new column, incurs_seek_penalty, was introduced in the SQL Server 2019 sys.dm_os_volume_stats DMF. This tinyint column is not currently documented.
Does anyone have information about the enumerated values returned in this column? I've seen values of 0 (bare metal server) and 2 (VM). I'm curious as to what these values mean and what other values might be returned. My internet search hasn't turned up anything.

This is now officially documented, with the following values:

Indicates the type of storage supporting this volume. Possible values are:
0: No seek penalty on this volume, typically when the storage device
is PMM or SSD
1: Seek penalty on this volume, typically when the storage device is
HDD
2: The storage type can't be determined when the volume is on a UNC
path or mounted shares
NULL: The storage type can't be determined on Linux operating system
Applies to: SQL Server (starting with SQL Server 2019 (15.x))

It looks like this is related to the low level DEVICE_SEEK_PENALTY_DESCRIPTOR structure (thanks, LowlyDBA!) - possibly in an attempt to check if a drive is an SSD or not (that approach is also discussed on Stack Overflow).
It would still be interesting to know if this information about the storage is (or will be) used by SQL Server (e.g., for updated I/O cost estimates, or performance optimizations inside the storage engine).


How to avoid row locks on both tables joined in an update? I have little knowledge about MySQL internals, but I'm experiencing that in a query like:

UPDATE t1
  JOIN t2 ON t1.id = t2.id
SET t1.a = x, t1.b = y, [, ...]
WHERE [ ... ]

rows from both t1 and t2 are locked (they're blocking other SELECTs), although values from table t2 are only read in, they never change.

Since I usually do updates joining critical tables (that are involved in several queries and whose data is most of the times only read, I must get rid of those locks: is there any way to do that?
Should I rewrite those updates with subselects instead of joins?

You don't say what engine the tables are using (show create table $db.$table_name; ).

If you are using the MyISAM engine, you can manually lock the tables.  This isn't recommended for InnoDB tables because you will be over-riding the row level locking mechanism.

If you lock one table, you need to acquire a lock on all you use within the same lock command.  The command to lock both t1 and t2 for your purposes is...

lock table t1 write, t2 read;

After making your changes, you will want to immediately release the locks...

unlock tables;


What structure should be used to store data with multiple category identifiers? I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following:

ID   |   Place   |   Phone   |   Email   |   Category )

Now, I want to redesign the "Category" section to be able to include any given row in multiple (searchable) categories.

For example, let's say that there is a place called 'Lakeside Picnic Area.' Currently it is classified as 'Nature Reserve'. So, when people are searching for a nature reserve, it shows in their search results. However, I also want it to show up in "Food Area", "Wildlife", and "Viewing Point".

My question is this: How do I design my table/database structure to allow all of the data entries/rows to be searchable by multiple categories?

If a particular place can belong to multiple categories, you need to implement many-to-many relationship between places and categories. Usually it's done with link table:

Category(category_id(PK), category_name);
Place(place_id (PK),place_name,phone,email);
Place_Category(place_id (FK to place),category_id(FK to category), PK(place_id,category_id));

You may also want to mark a particular category as a 'primary' category for a place. There are two approaches , 1st is to have primary_category_id in Place, 2nd is to add a field to Place_Category , say relation_type ...


Can I disable execution plan caching for debugging purposes? I am trying to isolate poor performing queries in our application.

While query execution plan caching is great in production it is interfering with our debugging efforts.

Is there are way to temporary disable execution plan caching in SQL Server (any further) so I can get a reliable & true indicator of the parse & compile time on SQL each time I run the query batch?

According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure.

WITH RECOMPILE

This is probably the most direct way to force a recompile, if you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time. There are some significant disadvantages to watch out for though.

CREATE PROCEDURE dbo.FooForProfit @Blood INT, @Sweat BIGINT, @Tears SMALLINT
  WITH RECOMPILE
AS
  -- Do that foo-do that you do so well (for profit)!

Alternatively you can just add the hint after the execute thusly:

EXECUTE dbo.FooForFun @Joy = 42 WITH RECOMPILE;

The problems:

This will hurt performance, and most likely give you tainted performance values.
Adding WITH RECOMPILE after the EXECUTE statement might not be possible depending on how you're executing the stored procedures.
The compiled plan will not be cached, nor will the performance information be maintained1.

  The compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats.

This will disable the Parameter Embedding Optimization1.

  Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing. The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.

I feel it is important to note that some very smart and experienced people like Aaron Bertrand make it a point not to go down this route. I initially thought this would be your best bet I'm now convinced that this probably isn't the ideal solution.

SP_RECOMPILE

If you can't or don't want to modify the stored procedure and your data access mechanism doesn't let you append the query hint you can manually force a recompile of the stored procedure using another provided stored procedure. One nice up side to this route is you can force a recompile at will so you can easily compare executions with or without the compilation penalty.

EXEC sp_recompile N'dbo.FooForFame';

The problems:

This is going to add a separate step to your process to reset your test bed.
You could probably add this to a trigger linked to executing your stored procedure. <-- Don't do that because you don't want to encourage the maniacs or create more.

RECOMPILE Query Hint

Like it says in the docs, this will give you more fine grained control over what gets recompiled if a stored procedure has multiple queries.

  RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. RECOMPILE is also useful when you create plan guides.

It will also let you recompile statements that aren't within a stored procedure. That is useful for obvious reasons. Here is a sample:

SELECT
  Peace
FROM dbo.TheWorld
OPTION (RECOMPILE);

The problems:

This will be slightly more work if you really do have multiple statements in a stored procedure.


Change collation on partition column (SQL Server 2017) I'm trying to change collation on column:

ALTER TABLE [dbo].[yourdad] ALTER COLUMN [yourmam] varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL;

and it gives message like this:

  Msg 5074, Level 16, State 1, Line 1 The object 'yourdad' is dependent
  on column 'yourmam'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE
  ALTER COLUMN yourmam failed because one or more objects access this
  column.

It has partition schema and function that depend on this column.

Currently I have like over 40k table with such columns where collation should be changed.

Is it possible to change collation without a table recreation?

Drop the partitioned index(es) - deleted.
But when I'm trying to delete schema:

DROP PARTITION SCHEME yourdad

  Msg 7717, Level 16, State 1, Line 1 The partition scheme
  "yourdad" is currently being used to partition one or more
  tables.

It's a new case insensitive database collation on database I wan't to install, old one was - case sensitive.

You don't need to drop the table(s).

One option (that I have tested) is:

Drop the partitioned non-clustered index(es); move partitioned clustered indexes, primary keys, and unique constraints to a non-partitioned file group; and unpartition heaps by creating a (temporary) clustered index on a non-partitioned file group. For help identifying which indexes are affected and what action needs to be taken for each one, please see the query I have posted in the "Finding and Fixing Ignored Objects" section of "Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?" (which also contains examples of dealing with clustered indexes, contraints, and heaps).
Drop the partition scheme(s)
Drop the partition function(s)
Alter the column definition with the new collation
Drop and recreate the partition function to specify the COLLATE clause with the new collation for the input parameter (assumption here is that you are not changing the default collation of the database, and the input parameter will use the database's default collation for string types unless specified using the COLLATE clause, and the CREATE INDEX statement using this scheme and function will fail if the collation of this parameter does not match the collation of the partitioning column). Meaning, if you change the column's collation to SQL_Latin1_General_CP1_CI_AI but the database is still using SQL_Latin1_General_CP1_CI_AS, then you need to:

CREATE PARTITION FUNCTION PartitionFunc
(
  VARCHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AI
)
...

Now, 40k+ tables is perhaps more than a few, but if they are all using the same partition scheme and function, then this is only 4 extra statements (drop and create for the partition schema and partition function) compared to what you already need to do even if partitioning wasn't being used (i.e. you would need to drop and recreate the indexes no matter what). So, while this does require a lot of I/O as the indexes are dropped and recreated, there is no way around that when changing a column's collation.

I am assuming (and have fingers crossed) that these columns are not:

part of the clustered index
key fields in a primary key or unique index/constraint that is foreign-keyed to

If either of those cases is true then you have a lot more work to do, but that has nothing to do with the partitioning.

Another option (given the desire to not recreate everything) might be to use the undocumented sqlservr.exe -q option to change the collation on ALL user columns and databases on the instance. This approach changes the meta-data for the database, columns, parameters, etc, and then rebuilds the indexes (again, no way around this when changing collations, so with 40k+ tables this will likely take a few minutes (or more) and the database, or the entire instance if you update the collation globally, will be offline during that time).

I just tested this scenario on SQL Server 2019 RC1, and it does not work for partition functions, but that doesn't mean that this approach isn't part of the overall solution. It just means that you really have no choice but to go through the process noted in the section above. The sqlservr.exe -q approach will handle everything else (except for user-defined table types, I believe). There are plenty of restrictions on being able to change a column's collation, and even more on changing the database's default collation (lots of dependencies in both cases), so if you are wanting to have a case-sensitive database now, then likely you will have other obstacles besides just the partitioned columns.

The thing that doesn't work with partition functions is that the collation of the input parameter is stored in sys.partition_parameters. This particular meta-data is not changed with the otherwise-awesome-even-if-undocumented -q option. The effects this has are:

Assuming the data is either VARCHAR but no change of code page, or NVARCHAR, then the data will still be partitioned as it was before. For example, let's say that you have a RANGE LEFT partition function using FOR VALUES ( 'D', 'H', 'O', 'V' ). In this case, partition #2 contains values > D and <= H.

When using the case-insensitive collation of SQL_Latin1_General_CP1_CI_AI, partition #2 would contain E, e, F, f, G, g, H, h. Simple enough, right?
However, when using the case-sensitive collation of SQL_Latin1_General_CP1_CS_AI, partition #2 would contain d, E, e, F, f, G, g, H. This partition now contains lower-case d, and does not contain lower-case h. This is due to most of the SQL Server collations sorting upper-case before lower-case (Windows collations sort lower-case first).

If data is VARCHAR and the code page is different, then things might get interesting. Any characters with values between 128 and 255 can potentially either have a different value and/or sort position, be mapped to a similar character because it is't available on code page of collation used for partition function collation (i.e. "best fit" mapping), or it might be missing on the old code page hence it becomes a "?" and sorts as that. Depending on what characters are being used, how they map (if they even do) to the other code page, and how the partition ranges are set up, you can experience anything ranging from "no effect at all" to "really strange and difficult to trace/debug behavior".

I have submitted a request to have this particular meta-data added to the sqlservr.exe -q operation, but Microsoft has no real incentive to fix it given that it's an undocumented feature. So, please support it (i.e. vote for it) so they can at least see that people do want it (thanks!):
Update Collation of Partition Functions and User-Defined Table Types via SQLSERVR -Q

That being said, the two main questions are:

What is the effect of changing the collation in the first place? What is the current collation and what is the new collation?

If the code page is changing, will there be data loss (the example code in the question shows a VARCHAR column, which makes this a possibility; not so for NVARCHAR)? This approach does not do a code page conversion, so characters with values between 128 and 255 might become some other character, and there are no warnings/errors when this happens (but if you don't have such data, then this is a non-issue).

If sensitivities are changing (i.e. going from Accent-Sensitive to Accent-Insensitive) then will there now be violations of uniqueness in an existing unique index/constraint?
Do you want the new collation used for EVERYTHING? If so, this might work. If not, then this approach is not viable as it changes EVERYTHING.

For complete details on this approach, please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? (be sure to review the "Finding and Fixing Ignored Objects" section which contains queries to help with fixing partition functions)

If you have multiple databases and want to only change this one (again, EVERYTHING in this database, including the database's default collation), then you can:

Detach (or backup) this database.
Create a new, temporary instance just for making this change, using the same instance-level collation being used for the instance that this database currently belongs to.
Attach (or restore) the database to the new, temporary instance.
Update the temp instance's collation using the sqlservr.exe -q method.
Detach (or backup) the database from the temp instance.
Re-attach (or restore) the database to the instance it came from.


Condition in WHERE vs condition in CONNECT BY Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
  start with PARENT_ID = '11025'
  connect by parent_id = prior concept_id
  and exists (..)

In the second one, the exists predicate is moved from the connect by clause to the where clause.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
where exists (..)
 start with PARENT_ID = '11025'
 connect by parent_id = prior concept_id;

After reading the documentation, I came to the following conclusions.

One difference between the two queries is that the first query has two condition in the connect by , in order to identify a relationship - a row must fulfill not only the parent_id = prior concept_id condition but also the exists clause.

 connect by parent_id = prior concept_id
 and exists (..)

While the second one has only one condition.

 connect by parent_id = prior concept_id;

The part of the query that makes the distinction more obvious is the start with.

In the first query, all the rows with PARENT_ID = '11025' will be returned and used as root rows. The two conditions of the connect by will be used to find the children of those rows. The exists condition will not be applied to the root rows.

On the other hand, the second query will fetch the rows with PARENT_ID = '11025' and filter them with the exists statement.

As a result, the results will be totally different.


Which version of VB does MS Access use? Which version of VB does MS Access use in the code editor?  Is it VBA, VB.Net, other? I sometimes get confused when searching for help/solutions. For instance, in Access, you can't do:

Dim s as String = "hello"

But you can do:

Dim s
s = "hello"

All Microsoft Office applications use VBA.  Excel, Access, Word, Outlook, and Powerpoint can be scripted using VBA.  There are ways to extend this to shell scripting, ActiveX, etc and, by extension, a good number of other languages that those extensions can tap you into.  I embed SQL into Excel VBA macros.

VB has a slightly different syntax and a different IDE.


Is this amount of disk IO expected with MySQL? I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage).  The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 million IO requests.  This seems abnormally high considering there is one 2 MB database used for Wordpress.  I've used the site very lightly, just some editing in the backend, no live traffic.  Here is the memory available:

# free -t
            total       used       free     shared    buffers     cached
Mem:        615376     531848      83528          0       2296      58864
-/+ buffers/cache:     470688     144688
Swap:            0          0          0
Total:      615376     531848      83528

If I have around 83 MB of free space in memory why would MySQL not keep the 2 MB database there?  The reason I suspect MySQL for the high IO activity is when I run iotop mysqld always shows having the most activity.  Here is my config:

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
query_cache_type=1
query_cache_limit=2M
table_cache=1024
join_buffer_size=4M
thread_cache_size=128
tmp_table_size=64M
max_heap_table_size=64MB

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Is there any way besides iotop to know if MySQL is using memory vs disk?  Does anything in my configuration look wrong for my system?

Update: Ran query from answer below and output was:

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| InnoDB         |             1.469 MB |             0.625 MB |             2.094 MB |
| Total          |             1.469 MB |             0.625 MB |             2.094 MB |
+----------------+----------------------+----------------------+----------------------+

In /var/lib/mysql:

df -h .

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1            7.9G  3.8G  4.1G  49% /

ls -l

total 28688
drws--S--- 2 mysql arithmakids     4096 Dec  5 05:07 arithmakids
-rw-rw---- 1 mysql mysql       18874368 Dec 13 23:03 ibdata1
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile0
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile1
drwx------ 2 mysql mysql           4096 Apr 18  2012 mysql
srwxrwxrwx 1 mysql mysql              0 Dec 13 22:54 mysql.sock
drwx------ 2 mysql mysql           4096 Apr 18  2012 performance_schema
drwx------ 2 mysql mysql           4096 Apr 18  2012 test

Based on this what would be good settings for my.cnf?

MySQL Disk USage
SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

This will tell you exactly how much diskspace (in GB) MySQL is actually using for tables.
Please see my other post CPU usage on RDS instance monotonically increasing with no change to query volume on additional Storage Engine diskspace queries.
You also need to do this
cd /var/lib/mysql
df -h .
ls -l

Take note of ibdata1, ib_logfile0, ib_logfile1, and other files. You will also see how much disk is free for the disk holding /var/lib/mysql
Please run ls -l /var/log/mysqld.log to see if the log is too big.
MySQL Memory Usage
You have
innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M

That's 192MB. Since you have a lot of other defaults, you only have about 100 or 131 max connections. I recommend downloading MySQL Administrator for Free and watching memory usage in other aspects.
Epilogue
Please look over my Amazon RDS posts for other ideas

Should I increase max_connections in AWS RDS t1-micro for MySQL?
When should I think about upgrading our RDS MySQL instance based on memory usage?


Oracle SQL - CASE in a WHERE clause Is it possible to somehow do this?

WITH T1 AS
(
   SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
   SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
   select 6 as seq, 'SOMETHING 4' AS SOME_TYPE from dual
)
, T2 AS
(
   SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
   SELECT 'B' AS COMPARE_TYPE FROM DUAL
)
SELECT T2.*, T1.*
FROM T1, T2
WHERE  CASE T2.COMPARE_TYPE
         WHEN 'A'
            THEN T1.SOME_TYPE LIKE 'NOTHING%'
         ELSE T1.SOME_TYPE NOT LIKE 'NOTHING%'
      END

I know that my WHERE is clause is not correct.

Any help would be great in knowing if this type of statement is possible.

I don't want to write a Dynamic SQL.  If I have to I will write 2 different SQL statements.

Thanks

Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.

I'm guessing that you want something like

SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> /

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

But I'm making a lot of guesses about what your code is supposed to mean.

That this appears to be identical to a question someone asked in the OTN forums.  My answer is the same in both places.


MongoDB refuse to start, operation not permitted When I try to run mongod --dbpath=./data I get the following error:

2017-09-20T21:47:55.720+0200 I CONTROL  [initandlisten] MongoDB starting : pid=20500 port=27017 dbpath=./data 64-bit host=debian
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] db version v3.2.11
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] git version: 009580ad490190ba33d1c6253ebd8d91808923e4
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2l  25 May 2017
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] allocator: tcmalloc
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] modules: none
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] build environment:
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten]     distarch: x86_64
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten]     target_arch: x86_64
2017-09-20T21:47:55.722+0200 I CONTROL  [initandlisten] options: { storage: { dbPath: "./data" } }
2017-09-20T21:47:55.781+0200 I -        [initandlisten] Detected data files in ./data created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2017-09-20T21:47:55.802+0200 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=1G,session_max=20000,eviction=(threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2017-09-20T21:47:55.894+0200 E STORAGE  [initandlisten] WiredTiger (1) [1505936875:894516][20500:0x7f357e7c1cc0], file:WiredTiger.wt, connection: ./data/WiredTiger.wt: handle-open: open: Operation not permitted
2017-09-20T21:47:55.895+0200 I -        [initandlisten] Assertion: 28595:1: Operation not permitted
2017-09-20T21:47:55.895+0200 I STORAGE  [initandlisten] exception in initAndListen: 28595 1: Operation not permitted, terminating
2017-09-20T21:47:55.895+0200 I CONTROL  [initandlisten] dbexit:  rc: 100

The contents and permissions of the ./data directory is as follows:

drwxrwx--- 1 root vboxsf  4096 Sep 20 05:11 .
drwxrwx--- 1 root vboxsf  4096 Sep 20 22:07 ..
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:25 collection-0--661121965341019305.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 collection-0--9187569579836644431.wt
-rwxrwx--- 1 root vboxsf 16384 Sep 19 05:24 collection-2--661121965341019305.wt
drwxrwx--- 1 root vboxsf     0 Sep 19 19:16 diagnostic.data
-rwxrwx--- 1 root vboxsf 16384 Sep 19 05:24 index-0-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 16384 Mar  1  2017 index-1-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 index-2-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:25 index-3-7518289071480072689.wt
drwxrwx--- 1 root vboxsf     0 Sep 20 05:12 journal
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:24 _mdb_catalog.wt
-rwxrwx--- 1 root vboxsf     0 Sep 20 05:11 mongod.lock
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 sizeStorer.wt
-rwxrwx--- 1 root vboxsf    95 Feb 23  2017 storage.bson
-rwxrwx--- 1 root vboxsf    49 Feb 23  2017 WiredTiger
-rwxrwx--- 1 root vboxsf  4096 Sep 19 05:24 WiredTigerLAS.wt
-rwxrwx--- 1 root vboxsf    21 Sep 20 04:49 WiredTiger.lock
-rwxrwx--- 1 root vboxsf  1000 Sep 19 05:27 WiredTiger.turtle
-rwxrwx--- 1 root vboxsf 61440 Sep 20 05:12 WiredTiger.wt

My user is in the vboxsf group.

Does anyone know why I'm getting this error, or what I can do to fix this?

I should note that I'm running Debian inside Virtual Box on a Windows 8.1 host. The directory in question is a shared folder (hence the vboxsf group).

A summary of possible solutions are listed below for your convenience:

MongoDB Operation Not Permitted

  MongoDB was working perfectly until one day it generated a lock file and stopped starting with the following error:

The corresponding accepted Answer was:

  Assuming permissions are correct. It could be an selinux issue, try:

setenforce 0

  then try to start the server. If the server starts, you will have to use semanage to set the proper context to the mongo data folder

Windows mounting /data/db

  I'm trying to mount /data/db from my Windows dev PC but everytime I try, Mongo crashes as it seems to not have the right BUT :

  I have changed the security parameters in windows to allow everyone to do everything (full access)
  If I mount in a different directory, it works (the container launchs itself and I can write in my directory)

A possible explanation was:

  Found a link: VirtualBox Shared Folders are not supported by mongodb.

Apparently one user found a workaround and documented it as:

  It works using a volume which is created using docker volume create --name=myvolume
  The volume name is used instead of the local path. It's just a workaround.
  To be more precise the line I used in my case :

docker volume create --name=mongodata
docker run -d -p 27017:27017 -v mongodata:/data/db --name=mymongo mongodb:3.3

Mongodb with volume doesn't work (Operation not permitted)

The initial person writing the issue had already determined that it wouldn't work and was in search of a solution.

  Mongo docs says For example, HGFS and Virtual Box’s shared folders do not support this operation.
  https://docs.mongodb.com/manual/administration/production-notes/#fsync-on-directories

  I wonder if it is possible to make work on current docker for windows.

There was a link in this case that recommeded tuning the mongodb storage engine:

    Doesn't docker plan to implemnt something like this?

  No, not right now. You might be able to tweak the mongodb storage engine to work on an SMB share: https://forums.docker.com/t/issues-with-mongo-with-mounted-volume-for-data/15085/4

Summary

There are multiple issues surrounding MongoDB and virtual servers on Microsoft Windows, so you might want to consider running your MongoDB in a *nix environment.


pg_upgrade fails with an unspecified error I’m running Postgres 9.1’s pg_ugrade to migrate from 9.0 to 9.1 on Windows x64. After editing the permissions to allow full trust, running the tool as the postgres user, and working around a bug whereby pg_upgrade leaves the "old" store locked and refusing to run again ("there seems to be a postmaster servicing old cluster"), it now fails without explaining what’s wrong:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system oid user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok

There were problems executing ""C:\Progs\Postgres\9.0\bin/pg_ctl" -w -l "nul" -D "C:\Progs\Postgres\9.0-data"  stop >> "nul" 2>&1"

Failure, exiting

Is this tool production-ready or am I wasting my time? If this is what I should be doing, what are your suggestions for solving the current failure?

Basically pg_upgrade is production ready.

Unfortunately the early builds of 9.1 had some bugs related to pg_upgrade and Windows. These were fixed in 9.1.3 as far as I know.


How does a VoltDB cluster determine time sync between nodes I have a weird painful issue with VoltDB clusters on multiple RHEL7.1 vm (KVM).

Time is synced between the VM and their hypervisor with a single local master NTP server - using chrony. Master server sync with external ntp pool.

There is a random error generated by VoltDB:

  FATAL [main] HOST: Clock skew is 120 which is > than the 100 millisecond limit. Make sure NTP is running.

Chrony config has been setup as much as possible as per the perf guide from VoltDB for ntpd - here.

I have searched high and low with my "google-fu" - but I can't seem to find an answer on how VoltDB determines that clock skew error.

The only closest one I can find is in this java code: voltdb/SocketJoiner.java on github from line 603 onwards.

skew = System.currentTimeMillis() - currentTimeBuf.getLong();

I wonder why VoltDB doesn't use a java function call - System.nanoTime() instead, for a precision timing.

Any help or insights will be great.

At startup, VoltDB is going to send a round-trip message from the first node of the cluster to all joining nodes of the cluster. This message queries the local time at each node.

For each joining node, the difference between its local time and the first node's local time is computed. This includes clock skew and network latency. In fact, it's possible for clock skew and network latency to cancel each other out, but not super likely.

Across all joining nodes, VoltDB reports the maximum difference between clocks.

In this case, VoltDB is telling you that for some pair of nodes including a joining node and the first node you started (the startup leader), the difference between the compared clocks was 120ms, which is a lot for machines on NTP that are in the same datacenter.

You can check network latency using ping between nodes, but make sure you're using the same network interface you've setup VoltDB to run on.


How do I update a table with the aggregated values (min, max) of a related table, fast? I have a table

create table w (
  id integer primary key,
  --
  min_xyz double,
  max_xyz double,
  min_abc double,
  max_abc double
);

with the min* and max* values currently being null.

I have another table

create table n (
    id   integer primary key,
    ---
    xyz  float,
    abc  float
);

There is a n:n relation between these tables:

create table n_in_w (
  n_id   integer not null references n,
  w_id   integer not null references w
  --- further attributes
);

Now, I'd like to fill the min* and max* attributes in w with the respective values in n via the connection via n_in_w. The following would do

update w set
  min_xyz = (select min(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_xyz = (select max(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  min_abc = (select min(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_abc = (select max(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id);

but I fear that the performance of this sql query is not optimal as it has to scan n 4 times for each n.id;

Ideally, I am looking for something like

update (
  select
    w.id,
    /*----*/
    w.min_xyz,
    w.max_xyz,
    w.min_abc,
    w.max_abc,
    /*-----*/
    min(n.xyz) min_n_xyz,
    max(n.xyz) max_n_xyz,
    min(n.abc) min_n_abc,
    max(n.abc) max_n_abc
  from
    w, n_in_w, n
  where
    w.id = n_in_w.w_id and
    n.id = n_in_w.n_id
) i
set
  i.min_xyz = i.min_n_xyz,
  i.max_xyz = i.max_n_xyz,
  i.min_abc = i.min_n_abc,
  i.max_abc = i.max_n_abc;

(or something similar), but it seems it's not possible with mysql.

MySQL syntax:

UPDATE
      w
  JOIN
      ( SELECT
              n_in_w.w_id,
              MIN(n.xyz) AS min_n_xyz,
              MAX(n.xyz) AS max_n_xyz,
              MIN(n.abc) AS min_n_abc,
              MAX(n.abc) AS max_n_abc
        FROM
              n_in_w
          JOIN
              n
            ON
              n.id = n_in_w.n_id
        GROUP BY
              n_in_w.w_id
      ) nw
    ON
      nw.w_id = w.id
SET
      w.min_xyz = nw.min_n_xyz,
      w.max_xyz = nw.max_n_xyz,
      w.min_abc = nw.min_n_abc,
      w.max_abc = nw.max_n_abc;


How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items Short Description

I have a database of special offers that bundle several items together.  I want to be able to search for a list of specific items and get a combination of bundles that includes all those items with the fewest unwanted extras.

Example

My database has bundles like these:

Bundle 1: Item A X2, Item B X1, Item D X4
Bundle 2: Item A X5, Item C X1, Item E X3
Bundle 3: Item B X2, Item C X3, Item D X3
Bundle 4: Item C X10, Item D X1, Item F X4

I want 10 of Item A, 4 of Item C, and 1 of Item E.  I want to search for a combination of bundles that yields the items I want with the fewest extra items.  The items I want can vary and should be easily editable in the search.

I don't think anyone has come up with a complete answer for your question. This sounds like a complex math question which does not belong on the "Database Administrators" site.

With that said, Joe Celko has an article that might help you. Bin Packing Problems: The SQL


how to restore sqlite dump file to sqlite database using java? I generated dump file from java using .dump command so my back-up is ready now I want to restore it to once again so can anyone help me how to restore?

In stackoverflow I got this but nothing restore from this..backup-and-restore-sqlite-from-disk-to-memory-in-java

so how to restore this file?

If you're using JDBC with Java, have a look at sqlite-jdbc
 by xerial. They added the backup and restore API of sqlite a while ago.

I use this together with an sqlite in memory-database for higher performance and regularly backup to a file.

You can use it like this:

// Use this connection string for an in memory-database
Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

connection.createStatement().executeUpdate("restore from database.db");
connection.createStatement().executeUpdate("backup to database.db");