texts: Very large subpartitions after table redefinition

texts:
I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size.

select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments where segment_type = 'TABLE' and segment_name = 'WERTEVAR';

segment_type | segment_name | SIZE_MB
TABLE          WERTEVAR       153.543.......

What I did:

CREATE TABLE WERTEVAR_TMP(
  ...column definitions
)
TABLESPACE "USERS"
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVTEIL, WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000',     'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            => 'CHYSTAT_STAGING_MONITORING',
        orig_table       => 'WERTEVAR',
        int_table        => 'WERTEVAR_TMP',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    => TRUE,
        copy_constraints => TRUE,
        copy_privileges  => TRUE,
        ignore_errors    => FALSE,
        num_errors       => l_errors,
        copy_statistics  => FALSE,
        copy_mvlog       => FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => 'WERTEVAR_TMP');
END;

DROP TABLE WERTEVAR_TMP;

Now when I look at the table size, it gets HUGE: about 23GB!!

select segment_type, segment_name, sum(bytes) / 1014 / 1024 as SIZE_MB from user_segments where segment_name = 'WERTEVAR' group by segment_type, segment_name;

segment_type       | segment_name | SIZE_MB
TABLE SUBPARTITION   WERTEVAR       23525.743.......

Where I can see that every single subpartition has exactly 8M. Did I something wrong? Are those subpartitions supposed to be large like this?

I can also see that those subpartitions have set:

PCT_FREE       = 10
INITIAL_EXTENT = 8388608
NEXT_EXTENT    = 1048576

I would change that INITIAL_EXTENT AND NEXT_EXTENT to be two orders of magnitude smaller. Something like 83886 and 10485. But I don't know if that is ok, or totally useless.

I would appreciate any hint.


texts:
Is there an official certification for Mariadb?
And if there is how can I find the next exam days?


texts:
To cut a long story short, I deleted several .idb files and then tried to re-import them from a .sql file.

I'd imagine there is some relic of the old tables somewhere. Does anyone know what to remove or if I can have MySQL re-build its internal tables. These are the only InnoDb tables I have; the rest are MyISAM


texts:
We've been using a linked server to an access database for close to a year now with little issue.  Now when I run a select query against the linked server the query sits there and never finishes.  Killing the session results in the familiar transaction sitting in Rollback/Killed status until the service is restarted.

(SPID 153: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.)

Is there a way to clear out these select statements or perhaps run them such that they don't end up in the rollback/killed status?

Has anyone else seen this sort of issue before?  What did you do to get around it?


texts: JOIN Call table to grouping by Origination and Destination

texts:
It's a call system.

  tables structure and data sample :

tbl_countries

CREATE TABLE tbl_countries
(
    country_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    country varchar(100) NOT NULL
);
INSERT INTO tbl_countries (country) VALUES ('Austria');
INSERT INTO tbl_countries (country) VALUES ('Brazil');
INSERT INTO tbl_countries (country) VALUES ('Denmark');
INSERT INTO tbl_countries (country) VALUES ('France');
INSERT INTO tbl_countries (country) VALUES ('Lebanon');

tbl_destination_clients

CREATE TABLE tbl_destination_clients
(
    des_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    des_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_destination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_destination_clients (country_id);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('961123456', 5);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('55123456', 2);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('45123456', 3);

tbl_origination_clients

CREATE TABLE tbl_origination_clients
(
    orig_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_origination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_origination_clients (country_id);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('33123456', 4);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('43123456', 1);

tbl_calls

CREATE TABLE tbl_calls
(
    call_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client_id int(11) NOT NULL,
    des_client_id int(11) NOT NULL,
    call_duration int(11) NOT NULL,
    CONSTRAINT tbl_calls_ibfk_1 FOREIGN KEY (orig_client_id) REFERENCES tbl_origination_clients (orig_client_id),
    CONSTRAINT tbl_calls_ibfk_2 FOREIGN KEY (des_client_id) REFERENCES tbl_destination_clients (des_client_id)
);
CREATE INDEX des_client_id ON tbl_calls (des_client_id);
CREATE INDEX orig_client_id ON tbl_calls (orig_client_id);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 5);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 6);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (2, 4, 9);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 1, 10);

 Attemps :

I try to use JOIN between the same table tbl_calls, like this :

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.des_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by
  co.country,
  cd.country;

But it's not working ;(

The Result of this sample data must be :

Origination  Destination  SUM(call_duration)
------------+------------+----------------
France       Brazil       11 -> (5+6)
Austria      Denmark      9
France       Lebanon      10

Thanks ..


texts: How to store number of years, months and days in PostgreSQL?

texts:
How to store number of years, months and days in PostgreSQL ?

Example: the life time of bulb is => 3 years, 4 months, 23 days

Is there any data type in PostgreSQL


texts: Understanding MySQL Innodb replication issue

texts:
I am trying to flip MySQL primary/ RW database from one host to other host. There are times where replication on the workers is broken post flip. Trying to understand the internal details and best possible implementation to have a smooth flip without breaking replication
Architecture
1 DB host and 2 active replicas (for failover). 20 worker nodes which server RO traffic.
MySql - 5.6 and logs setting in my.cnf file are default setting (never purge)
Steps for flip
For readability, host A - current primary; host B - current secondary being promoted as new primary

Replication is current across all worker nodes.
Make host A RO, verify GTID of host A is on host B
If GTID is not replicated, do a manual dump of host A and restore on host B
Promote host B as primary
Update cname of primary DB
Update replication across all workers to new primary
If replication is broken(below error), restore dump across broken workers (fall back scenario)

Step 5 detail mysql commands:
stop slave
change master to master_host="" master_port="" master_auto_position="" ...
start slave

Error for broken replication:
error 1236, Got fatal error 1236 from master when reading data from binary log:
'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1,
but the master has purged binary logs containing GTIDs that the slave requires.
Replicate the missing transactions from elsewhere, or provision a new slave from backup.
Consider increasing the master's binary log expiration period. The GTID sets and the missing
purged transactions are too long to print in this message. For more information,
please see the master's error log or the manual for GTID_SUBTRACT.'

Goals
Prefer to avoid step 6, as we each restore takes ~10 min and we have 20 hosts. Sequential restore takes around 200 minutes (200 min of no strong consistency => poor design)
Trying to restore asynchronously across all hosts - https://stackoverflow.com/questions/66763150/cat-unix-command-multithread-safe-looking-for-parallel-mysql-restore-on-severa
Questions

Why is replication broken some times where as it is not other times ?

Reading bin log on host B and updating master position would work but we might loose transactions.
Would cutting off replication from host A for workers resolve this ? host A is in RO mode though which would not have any new transactions.
Why is this transient issue ? We are opening host B to RW aggressively but host B would have logs and GTID transactions of worker nodes. Something wrong with auto position ?

If this is not a better approach any better way to restore replication across all hosts ?


texts: What is the sense and benefit of using SqlCommand.Prepare()?

texts:
I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this?

Sample:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

I have played around a little bit and traced. The execution of the Command after calling the Prepare() method makes Sql Server execute the following statement:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

After that when the Parameter gets it's value and SqlCommand.ExecuteNonQuery() is called, the following gets executed on Sql-Server:

exec sp_execute 1,@id=20

To me this looks like the statement gets kind of compiled as soon Prepare() is executed. I wonder what is the benefit of this?
Does this mean that it is put into the plan cache and can be re-used as soon the final query is executed with the desired parameter values?

I figured out (and documented it in another question) that SqlCommands that are executed with SqlParameters always are wrapped in sp_executesql procedure calls. This makes Sql Server able to store and reuse the plans independant of the parameter values.

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here?


texts: DynamoDB query by flag which tells if it's time to re-process the item

texts:
I have a table of stuff and some of it needs to be processed with cron daily or every minute. On SQL table I would add column last_time_processed and save timestamp in it. The query would take rows like

SELECT * FROM table WHERE last_time_processed < 123456
and would add an index on this column.

How to do the same thing on DynamoDB? I have to select primary key to make a query and primary keys are different for different rows and I need to select all rows where last_time_processed < 1234.

The scan would make a job but it would read the entire table, which will eat a lot of RCU.

Thinking about another way to add an extra column which would have fake-primary-key-x and x would be anything between 1..10 so, in theory, it would spread GSI to 10 nodes and bypass 10GB limit/node. And then query on each primary key + sort by last_time_processed but it would require several queries to make full select.

Any ideas?


texts: Oracle TRANSLATE Not Returning Expected Result

texts:
We are trying to execute TRANSLATE on a couple hundred rows of data. We have a temporary table containing ID's to rows that need to be updated using TRANSLATE. The odd part is if we attempt to select say two rows, the command is executed properly and the information is updated. However, when we try to run it against 200 rows, it says that 200 rows were updated, but in inspecting the data, they have not been updated. Is there a limitation to the number of rows that can be updated using TRANSLATE in a single query?

We're doing something like:

UPDATE TABLE_A SET COLUMN_TO_UPDATE =
TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);


texts:
I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this.

I'm setting up a weekly DB maintenance job for a 9.3 server.

Part of this will be a VACUUM FULL. It's a smallish DB and I have a decent weekend maintenance window so I can run the FULL without any issues.

Is there any point in adding the ANALYZE option to the command?

According to the 9.3 documentation:

  VACUUM FULL rewrites the entire contents of the table into a new disk
  file with no extra space, allowing unused space to be returned to the operating system.

If the table is completely recreated then do I need to specifically ask for ANALYZE to happen or are the statistics updated automatically as the rows are written to the new version of the table?


texts: MS-Access: How do I go about building a database with inconsistent data sources?

texts:
I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.

The data is all employee data, the problem is, I only have one file of seven with a unique Employee ID, and the format of Employee_Name is inconsistent across about half the files, so I can't join on Employee_Name (capitalization, commas, and initials are all inconsistent). Do I need to write macros to create consistency in Employee_Name so I can write the lookups to create the proper primary/foreign key relationships, or is there a more succinct way to do this?

My first choice is to go back to the client, and ask for new data exports with a common Employee_ID field across all of them, but I don't know if or when that's feasible.

I should also note, I already wrote a bunch of code in R to solve this problem and perform the joins, but the client needs a solution that doesn't rely on me running an R script every time they want to update their data.

I'm also all ears if there's a better solution with SQLite or something like that.


texts: Select statement to retrieve xml output as below

texts:
Could someone provide me some clues or solution to retrieve record sets like below?

Note: I read the msdn documentation but leads me no where but hair loss :(

Just for the assumption think i have 2 tables connected via Rid field

Table 1 columns:

 Rid, UserName, Hash

Table 2 columns:

 Rid, Phone, City, Email

Table1 and Table2 are connected via the Rid column.

I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in SQL Server 2005 Express.

Output expected:

<UserDetails>
    <Account>
        <UserName>
        </UserName>
        <Hash>
        </Hash>
    </Account>
    <Personal>
        <Phone>
        </Phone>
        <City>
        </City>
    </Personal>
</UserDetails>

@matt Please look into the procedure I created below. When you execute the stored procedure with code at start you will know the problem I face

stack_getusers  '<Request Type="GetUsers" CRUD="R">
                            <UserDetails>
                                <Rid></Rid>
                            </UserDetails>
                        </Request>'

CREATE PROCEDURE [dbo].[stack_getusers]
@doc NTEXT
AS
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    (
        SELECT t2.Phone AS "Personal/Phone",
               t2.City AS "Personal/City"
        FROM table1 t2
            INNER JOIN table2 t3 ON t2.rid = t3.rid WHERE t2.rid = t1.rid AND (xml.Rid = '' OR t1.rid = xml.Rid)
        FOR XML PATH('Personals')
    )
FROM table1 t1
    INNER JOIN table2 t2 ON t1.rid = t2.rid
OPENXML (@idoc,'/Request/Users',2)
WITH (Rid int) as xml
where (xml.Rid = '' OR t1.rid = xml.Rid)
FOR XML PATH ('UserDetails');
EXEC sp_xml_removedocument @idoc


texts: Restarting One Database Not Entire Server

texts: Is there anyway to restart one database in SQL Server instead of restarting the entire server?  I have a task in perpetual rollback on one of my databases.  I'd like to restart it without turning off / on the other DBs


texts:
I do not have install privileges on my machine.

Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on.

Is there a way they can change the User Instances from one Windows Authentication to another without having to reinstall?


texts: Can I drop and recreate a system extended stored procedure?

texts:
I am having a problem with one of my servers where I cannot execute xp_cmdshell under certain conditions. The full problem can be found here problem running xp_cmdshell when Application Name set in connection string

I want to see if dropping and recreating it will fix it (on a test server first). I'm grabbing at straws here.


texts:
Let me explain the scenario first before asking the question.

I have a database with a set of tables. One table, say Table A, contains username details, and other table, say Table B, contains user activity log. I want to delete the specific user log from Table B when that specific user entry is deleted from Table A. I can do this with a trigger, but there is a requirement that entries from Table B should be deleted after sometime (say after 5 minutes), not immediately.

Is it possible to delay trigger execution for 5 minutes? What could be the possible design for trigger. Whether deferred trigger will be helpful in this scenario.


texts: SQL Server 2012 performance v SQL Server 2008 R2

texts: Does anyone have any links to performance benchmarking of the database engine, or any anecdotal reports of the performance improvements in SQL Server 2012. We are only using the DB engine,not reporting services or BI. Any advice / gotchas from an upgrade point of view?


texts: Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

texts:
I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:

sudo pg_dropcluster 9.4 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

However, after running the second command I get the following error:

Error: could not get cluster locales.

How do I fix this?
(PS Sorry if this is a stupid question, and please let me know what additional information is required)

PS: locale (lc_collate) returns en_US.UTF-8


texts: SQL Server Service Accounts Max Character Length

texts:
I've been considering a new naming convention for all SQL Server service accounts for instances in our enterprise.  I ran into some issues on my lab environment.

Is there a character limit to SQL Server service account names?

DOMAIN\SQL-Service-COMPUTER-NAME
DOMAIN\SQL-Agent-COMPUTER-NAME
DOMAIN\SQL-SSRS-COMPUTER-NAME
DOMAIN\SQL-SSAS-COMPUTER-NAME
DOMAIN\SQL-SSIS-COMPUTER-NAME


texts: What's the most effective way to duplicate an instance on SQL 2008?

texts:
We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it for dev work when I'm not at the office. (The regular dev database instances are about 150GB, this one instance is 3GB)

What's the most repeatable/scriptable method for recreating the instance on my laptop? I'm sure I'm going to have to run through the installer again for SqlServer2008, and set up an instance, but after that ... I'm thinking I want to be able to drop my old databases every month or so and copy all the new stuff over to my laptop, as my instance won't change on either the one demo system or the laptop. But the databases themselves will update, with refreshes on the sprocs and the like.

I've considered scripting all the "drop database-files; add database-files {path}" and keeping them in a runnable batch file of some sort, and then I can "drop, delete, copy, add" but wondered if there was a better way than just xcopy and batch?

I'm trying to do this on the cheap (don't we always try and do things on the cheap) so I'm not particularly interested in RedGate products or the like. I want something I can maintain easily in a text editor for myself.


texts: Database atomic operations implementation

texts: The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this implemented internally?


texts: CPU Time Slower in SQL Server 2014 than in SQL Server 2008

texts:
We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v).  We were doing some performance testing and to our surprise it seems that CPU times are worse in the new server system compared to the old, crackling one.
These are the systems:

Old Server:

Runs in Virtual Machine VMWare ESX 5.1
Windows Server 2008 R2 32 bits
SQL Server 2008 R2
Quad Core Xeon ES-2620 2Ghz
5600MB RAM

New Server:

Runs in Virtual Machine Hyper-V UEFI Release v1.0
Windows Server 2012 R2
SQL Server 2014
Quad Core Xeon ES-2640 2.6Ghz
32766 MB RAM

A little history of what we did:

Two different benchmark tests shows that CPU is about 80% faster, memory access is about 100% faster and IO disc access is about 1000% faster in the new server.
Testing queries are using multiple joins with non-clustered indexes.
In both servers the testing database is exactly the same.
In both servers energy power option is set as High Performance and also checked with CPU-Z that the processor is always at max speed.
In both servers “locked pages” are enabled.
In both servers the statistics are properly updated and query plans are the same when executing the test queries.
To make sure that the plan are always the same we’re executing the queries in SQL Server 2014 with compatibility mode 100 (SQL Server 2008), but as well without this compatibility-mode the outcome is the same.
Max memory options is set to default value in both servers (2147483647) and we have also checked the parallelism (max degree is set the same for both and also the cost)
The new server is dedicated (there is nothing else running there)

After all these checks we’re getting better time results for cached queries (caused only by CPU time) still in the old server.

Test query:

SELECT ROW_NUMBER() OVER(ORDER BY c.field1, c.field2) AS RowNumber, a.t1_t3ID, c.field1, c.field2, a.t1_t2ID, b.field1, a.BeginDate, a.EndDate, c.field3
FROM table1 a
       INNER JOIN table3 c ON a.t1_t3_ID = c.t3_PKID
       INNER JOIN table2 b ON a.t1_t2ID = b.t2_PKID
WHERE '20140101' BETWEEN a.BeginDate AND a.EndDate

Query Execution Plan (is the same for both servers, with the same percentages):

Query Execution Plan Here

The Results for CPU time on both servers:

Old server:

CPU time = 93 ms,  elapsed time = 65 ms.
CPU time = 94 ms,  elapsed time = 99 ms.
CPU time = 94 ms,  elapsed time = 70 ms.
CPU time = 93 ms,  elapsed time = 63 ms.
CPU time = 93 ms,  elapsed time = 67 ms.

New server:

CPU time = 125 ms,  elapsed time = 309 ms.
CPU time = 141 ms,  elapsed time = 304 ms.
CPU time = 139 ms,  elapsed time = 288 ms.
CPU time = 156 ms,  elapsed time = 277 ms.
CPU time = 142 ms,  elapsed time = 323 ms.

  Note: we have also checked that the CONVERT_IMPLICIT is not affecting
  performance in this case.

  What we observed is as well if we force to use only one processor-core
  in the old server CPU time keep roughly the same. If we do same in the
  old server the execution-plan is switching to table-scans and CPU time
  increase up to 800ms.

Somebody have an idea what still can be checked/tested or at least have an explanation on this observation? Or is it for sure the virtualization systems (vmware vs. hyper-v) which cause such a difference in CPU time for databases?


texts: how to represent what functional dependencies are?

texts:
I'm trying to understand how to represent what functional dependencies are:

A1, A2,..., An → B
If two tuples of R have the same attributes for A1, A2,..., An then they have the same value for the attributes of B.

I am not able to represent it abstractly speaking... Can you give me an example?


texts: mysql: how to copy database and required dependencies

texts:
I need to set up an automated copy of a single mysql database (not the whole DBMS). If I use mysqldump to dump and recreate a DB, the copy may be unusable because the user accounts need to also be copied. Triggers in the source DB for instance may be copied, but there's no guarantee that they will execute correctly in the new DB because the original triggers were designed to run as some particular user.

A couple of questions:

how can I automate copying of the user accounts and permissions?
is there a way, when copying a db to check whether all of its dependencies are met? (E.g. are all of the databases referred to by the stored procedures present?)

I understand that I can replicate the whole DBMS - this is overkill (and often not practical for hourly regressions)


texts: Filtering the results of a SELECT query to avoid certain words in the value of a column?

texts:
I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the content.

For example

  porn, pornography, Adult, Boobs, Booty, Tits

pseudo query may be like

SELECT * FROM videos WHERE title NOT LIKE (porn, pornography, Adult, Boobs, Booty, Tits)

I checked NOT LIKE but I have to do it like

SELECT * FROM videos WHERE title NOT LIKE ‘%porn%’ AND title NOT LIKE ‘%pornography%’ AND title NOT LIKE ‘%Adult%’ AND title NOT LIKE ‘%Booty%’ AND title NOT LIKE ‘%Boobs%’ AND title NOT LIKE ‘%Tits%’

which is very inconvenient and inefficient. So is there any other better way to do this ?


texts:
Oracle 11.2.0.3 in Windows Server 2008 64 bits.
I try to set up archive mode in a database:

C:\Users\Admin>SET ORACLE_SID=BDSPROD

C:\Users\Admin>SQLPLUS /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 12:28:59 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect sys/oracle as sysdba
Connected.
SQL> select log_mode from v$database;

LOG_MODE
NOARCHIVELOG

SQL> ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest='D:\oradata\BDSPROD\archivelogs\' SCOPE=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SHUTDOWN IMMEDIATE;
ORA-01012: not logged on
SQL> STARTUP MOUNT;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6781128704 bytes
Fixed Size 2268064 bytes
Variable Size 3539993696 bytes
Database Buffers 3221225472 bytes
Redo Buffers 17641472 bytes
Database mounted.
SQL> ARCHIVE LOG START;
Statement processed.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select log_mode from v$database;

LOG_MODE
ARCHIVELOG

However, there is no archived redo logs generated in folder: D:\oradata\BDSPROD\archivelogs\
I also tried with SET log_archive_dest='D:\oradata\BDSPROD\archivelogs' (without last "\"), but same behaviour, no archived redo log is created


texts: How to change SQL Server Service Account using PS

texts: SQL Server runs under AD Account SQLAccount1. How to make it run under another account, SQLAccount2? How to do it un Powershell?


texts: Changing IP Addresses of an Always On Availability Group in SQL Server 2014

texts:
We have two nodes in an Always On availability group, on SQL Server 2014 Enterprise.

We need to change the IP addresses of the nodes, the cluster, and the listener.

They will still remain in the same domain.

The subnet mask shall remain the same.

All will the be the same except the IP Addresses and the default gateway.

What are the steps to accomplish this?


texts: Can I create a MySQL CSV table directly from an existing CSV file?

texts:
MySQL has a CSV storage engine.

I'm wondering if I could create such a table directly from an existing CSV file.

Made up command as example:

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV FROM FILE /file;

What I'm trying to achieve is an efficient way to load data that doesn't depend on LOAD DATA as that command opens up the possibility of arbitrary command execution.


texts: MariaDB (MySQL) Replication Slave Lags Behind a Certain Amount

texts:
I have 4 MariaDB replication slaves sync'ed with single master via SSL.  Slaves keep up-to-date, until somehow they permanently lag behind a certain amount of updates.  Has anyone seen this before?  Here's what I mean.  On the master:

SHOW MASTER STATUS \G

displays its current log position as 881134, then on each slave:

SHOW SLAVE STATUS \G

displays the master's Exec_Master_Log_Pos: 862183.  As you can see, the 2 numbers are quite different.  It never catches up on its own, no matter how long I wait.

If I issue a few more updates to the master, eventually the slaves will catch up to where I'm expecting them to, but they still remain several updates behind the master.

If I restart the slave MariaDBs, they catch up immediately and stay caught up for a few days until it happens again.

Things I have checked: No errors reported by the replication.  There is no CPU usage and no slow queries holding things up.  There is plenty of free memory and disk space on all MariaDB hosts.  No major network latency or outage.  Nothing in the MariaDB logs.  All slaves have separate server_id settings.  All are running INNODB. All system clocks are synchronized within a second of each other.


texts: What is considered as a document in mongoDB?

texts:
I have been learning MongoDB for the past two days and I've been confused with documents and their limits, and how to overcome the limits.

What is a difference between documents and collection?


texts: Export data using bcp to deal with corrupted data

texts:
I am getting consistency errors while running DBCC CHECKDB on a table that has corrupted rows on SQL Server 2014. I am running into corruption errors after the first 30,000 rows (total 1.4 million (14 lakh) rows).

I want to try to bcp the data out until I get to the corruption. I want to skip the corrupted rows. I want to bcp after the corruption range and minimize the data loss.

How can I do it? Please explain in detail.

See also my previous question: Consistency Errors running CHECKDB on a couple of tables in SQL Server 2014


texts:
I have been told that for data files backup operate at extent level and for log file, backup operates at page level.

I know that the file type for data file is always "Rows Data" and is stored in the form of extent(Either mixed or uniform extent) whereas logs are stored in the form of Log i.e. VLF(Virtual Log Files).

Can please somebody shed some light on this concept in a bit detailed level as I am a bit puzzled on how backup distinguishes between data and log. If its full backup, it would store all the committed change written to data file, for differential - all changes since last full backup from data file. And for log backup - all changes which is committed however not written to data file.

Appreciate your valuable input on this.


texts:
I've built a basic webpage to push data to the PostgreSQL database on AWS RDS from a CSV file. The issue is the docs they have provided use CLI to do that. If I use direct SQL queries using \copy command, it gives an error related to the superuser - must be a superuser to copy to or from a file Postgres rds. And if I use json structure to retrieve the data and push the data using the insert command for each entry, it inserts only 150 entries and gives an error of connection terminated. Also, it doesn't look like the go-to way for uploading CSVs containing 4000+ entries.
Also, the data will be inserted by non-tech guys so it must go through the website's easy interface where they upload the file and it pushes the data itself. I also looked at their s3 way but it uses cli as well which I believe will be complicated through the website.
So, how to upload CSV's data on AWS RDS PostgreSQL?


texts:
I have the following query (PostgreSQL 9.5.19):

with userlist as (
  select a.ips,
         array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
), shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users && u2.super_users
               order by ul.uid) as super_users
  from userlist u
)
select s.super_users, array_agg(distinct s.ips) as ips,
    (select sum(msg_count) from tmp_table_1 where super_user_id in s.super_users) as sum_msg_count
from shared_users s
group by s.super_users;

I get the following error:

ERROR:  syntax error at or near "s"
LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us...
                                                              ^

It seems like I cannot access s.super_users from within the subquery.

Anyone know how I can do this?

Here is a full example:  https://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1

Maybe I am not approaching this correctly?

Question

How can I add the additional columns (sum_msg_count, sum_user_count, max_last_date and pubs) to the result set?

Desired result

|super_users                      |  ips                                                        |sum_msg_count| sum_user_count  |   max_last_date                | pubs                        |
|---------------------------------|-------------------------------------------------------------|-------------|-----------------|--------------------------------|-----------------------------|
|{522460771,522460772,522460773}  |  {94.134.88.115,94.134.88.124,94.134.88.125,94.134.88.136}  | 30          |         9       |  "2018-06-30 21:29:48.664000"  |  {ad2,googlecooler,ads,ad1} |
|{522460774,522460775}            |  {127.0.0.1,5.5.5.5}                                        | 20          |         6       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ad3,ad1}     |
|{522460776}                      |  {8.8.8.8}                                                  | 10          |         3       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ads}         |


texts: MySQL: Assign rankings for a large table of results

texts:
In our games we have a quartely league system. Every 3 months, the current season ends and all scorings of the current quarter are archived into a table called archive_league. Each entry represents a user and his score for a given league quarter. It comes with the following fields:

id - the id of an entry
uid - the id of a user
rounds - the number of games the user played in the given quarter
score - the score of the user for the given quarter
rank - the position of the player, descending by score, in the given quarter
date - the date of the quarter this entry represents

My goal is to go through all entries for a given date and assign the field "rank" for results. For example the player with the highest score should receive rank = 1, 2nd highest score rank = 2 and so on. If 2 players share the same score, they should receive the same rank (olympic scoring).

Example:

player x with score 528 receives rank 7
player y with score 528 receives rank 7
player z with score 529 receives rank 9

I am currently achieving this goal with this query:

UPDATE archive_league
        LEFT JOIN
    (SELECT
        t.id,
            (SELECT
                    COUNT(id) + 1
                FROM
                    archive_league x
                WHERE
                    x.score > t.score
                        AND x.date = :quarter) AS new_rank
    FROM
        archive_league t) AS temp USING (id)
SET
    rank = new_rank
WHERE
    date = :quarter;

The problem is, however, with some hundreds of thousands of entries, this query runs for a couple of days, even though I have created indexes for the WHERE conditions. How can I optimize this query to run faster?


texts:
I have a MySQL database and I want to perform a little bigger search.
I have about 10k records in one of the tables and It's expected to grow, but slowly.
The biggest problem is that to perform the search I have to make a query with 4 JOINS which I think causes the search to be slow.
So here is some example struct:

[table records]
id INT unsigned PRIMARY KEY auto_increment
description text
label INT unsigned
type INT unsigned
price DECIMAL

[table records_labels]
id INT unsigned PRIMARY KEY auto_increment
label varchar

[table records_types]
id INT unsigned PRIMARY KEY auto_increment
type varchar

[table records_serial]
id INT unsigned PRIMARY KEY auto_increment
serial varchar
record INT unsigned

[table records_barcode]
id INT unsigned PRIMARY KEY auto_increment
barcode varchar
record INT unsigned

So here is how the things run:

I run a query which selects records.id, records.description, records.price, records_labels.label, records_types.type, records_serial.serial, records_barcode.barcode;
So the full query is like this:

SELECT
    records.id,
    records.description,
    records.price, records_labels.label,
    records_types.type,
    records_serial.serial,
    records_barcode.barcode
FROM
    records
    JOIN records_labels ON records_labels.id = records.label
    JOIN records_types ON records_types.id = records.type
    LEFT JOIN records_serial ON records_serial.record = record.id
    LEFT JOIN records_barcode ON records_barcode.record = record.id
WHERE records_serial.serial LIKE %SEARCH_TERM%
   OR records_barcode LIKE %SEARCH_TERM%

I think that the solution here is indexing I guess, but I'm not very familiar with it.
So shortly, how to speed up and optimize query of this kind?


texts: Good data format to store ISO 8601 recurrence in to mysql

texts:
I need to store ISO 8601 recurrence (something like R/20130325T06/P1D) to a table. this table stores different recurring jobs that needs to be executed.

Is there any native data type mysql (or any other db for the knowledge) that can be used to store this data other than VARCHAR data type.


texts: How to get a COUNT for both IN and NOT IN a table

texts:
I have a table that contains a master list of items and an associated table that contains a unique text string only on specific values (determined by the application that inserts the data).

Example:
    Master Table (Assets)

Asset_ID    IP Address           Operating System    Host Name
1           123.456.789.125      Windows             BobsComputer
2           987.654.321.126      Windows             TomsComputer
3           456.123.789.127      Windows             JennysComputer

The associated table Unique_ID looks like this, joined on the Asset_ID

Special_ID  Asset_ID    Unique_ID
1           1           xhsieriyfh0308487
2           3           098uindifnoei8384

What I am trying to get is a query that gives me a count of all items both IN Unique_ID and NOT IN Unique_ID
Essentially the output I'm looking for is:

With_Unique_ID      Without_Unique_ID
2                   1

I know it has something to do with making subqueries but I can't seem to find the right syntax/command order to make it work.
It's postgresql if that makes a difference.


texts: Split duration hourly depending on start and end time

texts:
I have a table that registers the status for equipment. The status has a start and an end time. But now I want to know for every hour what the status is. If the status was "Operating" from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day.

This is what I have now:

Shift_date  Status     Start_timestamp  End_Timestamp
----------  ---------  ---------------  ---------------
5/20/2017   Operating  5/20/2017 8:21   5/20/2017 10:40
5/21/2017   Delay      5/20/2017 10:40  5/20/2017 11:10
5/22/2017   Operating  5/20/2017 11:10  5/20/2017 13:50

This is what I want:

Shift_date  Hour  Status     Duration (minutes)
----------  ----  ---------  ------------------
5/20/2017   1     ..         ..
5/20/2017   ..    ..         ..
5/20/2017   9     Operating  39
5/20/2017   10    Operating  60
5/20/2017   11    Operating  40
5/20/2017   11    Delay      20
5/20/2017   12    Delay      10
5/20/2017   12    Operating  50
5/20/2017   13    Operating  50
5/20/2017   13    ..         ..
5/20/2017   14    ..         ..
5/20/2017   ..    ..         ..
5/20/2017   24    ..         ..


texts: Attempting to avoid SORT in query planner

texts:
I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):

SELECT foo from big_table, status_types
WHERE big_table.attr=1 AND
  lower(big_table.groupname) LIKE lower('Bar%') AND
  big_table.type = status_types.typeid
ORDER BY big_table.groupname DESC offset 0 limit 20;

 Limit  (cost=24511.42..24511.45 rows=14 width=150)
 ->  Sort  (cost=24511.42..24511.45 rows=14 width=150)
      Sort Key: big_table.groupname
     ->  Nested Loop  (cost=0.00..24511.15 rows=14 width=150)
           ->  Seq Scan on big_table (cost=0.00..24495.23 rows=14 width=72)
                 Filter: ((big_table.attr = 1) AND (lower(groupname) ~~ 'Bar%'::text))
           ->  Index Scan using typeid on status_types (cost=0.00..1.12 rows=1 width=86)
                 Index Cond: (status_types.typeid = big_table.type)

I have indicies on big_table for lower(groupname) and typeid is a PK for table status_types.
I would think it should be using the index on big_table (on groupname) to avoid sorting the results. I have tried several different options, even changing the query to something as simple as "select * from big_table where groupname LIKE 'foo%' order by group_name". Still performs the sort!!

I must be doing something wrong. Shouldn't the query planner favor the use of the index and NOT sorting?


texts: How to write a query that counts rows by date?

texts:
I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table.

I have used the following query to get the data of single day,

select count (*) from tx_feeder
where datum between (select sysdate -31 from dual) and (select sysdate -1 from dual);

SQL> 3455890

It will give the count for the whole month. But instead of getting the count of the entire month as a whole, I want to get the output like following way, total count of each day in a month from a single SQL query.

     sysdate -31 = 11190
     sysdate -30 = 13390
     sysdate -29 = 65790
     sysdate -28 = 44390
     ........
     sysdate -1 = 14590


texts: Send Email when sql server agent job fails

texts: How can I send an e-mail when the sql server job fails. I added my e-mail as operator and then in the job properties -> notification -> enabled the email. But it is not working. Any ideas?


texts: "Show table data" option to edit compact database

texts:
I have installed sql server compact edition 3.5 and i am sql server management studio to edit table.I am using "Show table data" option which comes on right click to a table of compact database. But i have another pc where on right clicking to table does not give option such as "Show table data". I know that some program installed in my pc causes this option to appear. So i want to know how i can get "Show table data" options for a sql server compact database?
Below is a screen shot of the option i am referring to.

Below is  a screen shot where that option does not show up.


texts: Case When over 2 columns return results into 1 output column

texts:
I have an issue in SQL Server (using 2014 but not sure that's relevant).  I have 2 variables Q20 and Q46 which depending on result I want to output a value to the same column for each result.  For example (The promoter column being the one that is generated with the Case expression)

Rf........Q20.........Q46........Promoter

1.............0.............9.............1

2.............8.............0.............1

3.............6.............0.............0

The Query I am trying to goes something like this (but obviously this isn't working):

select *
,case Q20
    when '8' then 1
    when '9' then 1
    else 0 end
,case Q46
    when '8' then 1
    when '9' then 1
    Else 0
end as Promoter
    from ##csat_resp

I have tried various connotations of this but nothing works that will bring both the results into one column (Promoter).

I refuse to believe this isn't possible and must be down to my lack of knowledge.

Please help!!


texts: How to easily bring 80 MySQL users into ProxySQL?

texts: I am setting up ProxySQL in front of a Percona MySQL server with 88 user accounts.  I don't even know most of their passwords.  Passwords are stored in MySQL as hashes but are stored in ProxySQL in plain text.  How can I easily bring in all 88 accounts into ProxySQL at once?  Or is there a pass-through authentication switch I can turn on?


texts:
I have a question related to behavior of WITH queries.

So I have

WITH test as (
    SELECT id, name, ....
    FROM test...
    WHERE ... )

In that auxiliary statement I select a column(s) that have index on them. So question is - If i select later on from test will it use that index?


texts: How to use a postgres variable in the select clause

texts:
With MSSQL it's easy, the @ marking the start of all variable names allows the parser to know that it is a variable not a column.
This is useful for things like injecting constant values where a select is providing the input to an insert table when copying from a staging table.
declare @foo varchar(50) = 'bar';
select @foo;

How do you express this for postgres?


texts:
I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed.

When should I reindex PostgreSql index? is there an index fragmentation value that I can query?


texts:
I have database which is set up in data folder of mongodb. For accessing this database I usually run the below command

    mongod --dbpath "C:\program files\mongodb\data" --port 27017

Now I am trying to replicate database. For creating Primary database I run command

    mongod --port 27017 --dbpath "C:\Program Files\MongoDB\data" --replSet replicaSetMGL --smallfiles --oplogSize 128

I have created two empty folders in mongodb folder as replicaSet1 and replicaSet2. Now for creating replica set for port no. 27018, I executed below command in another terminal.

    mongod --port 27018 --dbpath "C:\Program Files\MongoDB\replicaSet1" --replSet replicaSetMGL --smallfiles --oplogSize 128

This is not working (throwing error):

C:\Users\well come>mongod --port 27018 --dbpath "C:\Program Files\MongoDB\replic
aSet1" --replSet replicaSetMGL --smallfiles --oplogSize 128
2017-06-12T17:25:33.164+0530 I CONTROL  [initandlisten] MongoDB starting : pid=6
936 port=27018 dbpath=C:\Program Files\MongoDB\replicaSet1 64-bit host=hp
2017-06-12T17:25:33.164+0530 I CONTROL  [initandlisten] targetMinOS: Windows 7/W
indows Server 2008 R2
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] db version v3.4.4
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] git version: 88839051587
4a9debd1b6c5d36559ca86b44babd
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] OpenSSL version: OpenSSL
 1.0.1u-fips  22 Sep 2016
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] allocator: tcmalloc
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] modules: none
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] build environment:
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     distmod: 2008plus-ss
l
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     distarch: x86_64
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten]     target_arch: x86_64
2017-06-12T17:25:33.165+0530 I CONTROL  [initandlisten] options: { net: { port:
27018 }, replication: { oplogSizeMB: 128, replSet: "replicaSetMGL" }, storage: {
 dbPath: "C:\Program Files\MongoDB\replicaSet1", mmapv1: { smallFiles: true } }
}
2017-06-12T17:25:33.166+0530 I -        [initandlisten] Detected data files in C
:\Program Files\MongoDB\replicaSet1 created by the 'wiredTiger' storage engine,
so setting the active storage engine to 'wiredTiger'.
2017-06-12T17:25:33.167+0530 I STORAGE  [initandlisten] wiredtiger_open config:
create,cache_size=3557M,session_max=20000,eviction=(threads_min=4,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,lo
g_size=2GB),statistics_log=(wait=0),
2017-06-12T17:25:34.184+0530 W STORAGE  [initandlisten] Detected configuration f
or non-active storage engine mmapv1 when current storage engine is wiredTiger
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten]
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten] ** WARNING: Access contr
ol is not enabled for the database.
2017-06-12T17:25:34.185+0530 I CONTROL  [initandlisten] **          Read and wri
te access to data and configuration is unrestricted.
2017-06-12T17:25:34.186+0530 I CONTROL  [initandlisten]
2017-06-12T17:25:34.308+0530 I FTDC     [initandlisten] Initializing full-time d
iagnostic data capture with directory 'C:/Program Files/MongoDB/replicaSet1/diag
nostic.data'
2017-06-12T17:25:34.447+0530 I REPL     [initandlisten] Did not find local voted
 for document at startup.
2017-06-12T17:25:34.448+0530 I REPL     [initandlisten] Did not find local repli
ca set configuration document at startup;  NoMatchingDocument: Did not find repl
ica set configuration document in local.system.replset
2017-06-12T17:25:34.451+0530 I NETWORK  [thread1] waiting for connections on por
t 27018


texts:
I have the following query:

SELECT T2.Title
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
    AND T2.FKID2 = @ID_PARAM2
    AND CONTAINS(T1.Title, '"' + @SINGLE_WORD_PARAM +'"')

Title is of type NVARCHAR(100). All the IDs are INT.

My Execution Plan is such that it is using the Key Lookup (Clustered) operation 80+% of the time:

Predicate: TitleTable.FKID2 AS T2.FKID2 = @ID_PARAM2
Object: TitleTable T2
Output list: TitleTable.Title

Each and every mentioned column is in its own non-clustered index, Title is additionally in the full-text index and there's a clustered index on the primary key as well. Is there a way to include the full-text index in the covering index or something?

EDIT:


texts: Can I see the remote IP address for failed PostgreSQL logins?

texts:
We're running PostgreSQL on Heroku, with all the application logs going to Logentries, and I'm seeing around 4,000 failed login attempts per hour for a specific set of credentials:

2019-07-30T09:08:45+00:00 app postgres.3863 - - [ONYX] [6-1] sql_error_code = 28P01 FATAL: password authentication failed for user "xxxxxxxx"
2019-07-30T09:08:45+00:00 app postgres.3863 - - [ONYX] [6-2] sql_error_code = 28P01 DETAIL: Role "xxxxxxxx" does not exist.

The credential in question  (redacted here to xxxxxxxx) belonged to a former employee, who I think has left some sort of monitoring or dashboard process running on a private server or AWS account somewhere (yes, this is bad. We know. That's not what I'm asking about.)

There's nothing on any of our instances that's responsible for this traffic and I'm trying to identify where it's coming from. Problem is, the Heroku logs don't include any remote IP information or anything I could use to identify the source of these login requests, and because the credentials have been revoked they never establish a connection which would show up in pg_stat_activity.

Any way I can get the remote address of these requests from the PostgreSQL server stats or the Heroku logs so I can try to work out where the rogue connection attempts are coming from and hopefully get it shut down?


texts:
This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running.

I'm invoking the cobertura.sql file using the following sample CLI syntax:

psql -h localhost -U myUser -f cobertura.sql myDB

But it complains with the following error:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... cobertura.sql file:

DO language plpgsql $$
declare
    eq  record;
    sql varchar;
BEGIN

create table if not exists cobertura_tmp  (num integer, realtime char(1), lat numeric, lng numeric);

truncate table cobertura_tmp;
for eq in select imei_equipo as imei  from cliente_avl_equipo where id_cliente in (select id from cliente where nombre ilike '%enangab%') limit 3

loop

sql := format('insert into cobertura_tmp select count(*) as num, tipo as realtime, round(CAST(latitud as numeric), 4) as lat ,round(CAST(longitud as numeric), 4) as lng   from reports.avl_historico_%s where latitud between -38.67405472 and -36.75131149 and longitud between  -73.08429161 and -69.65333954 group by tipo, round(CAST(latitud as numeric), 4),round(CAST(longitud as numeric), 4)', eq.imei);

execute sql;

end loop;

update cobertura_tmp set num= -1* num where realtime='S';

create table if not exists cobertura_tmp_resumen  (num integer, lat numeric, lng numeric);
truncate cobertura_tmp_resumen;
--    select sum(num) as num , lat, lng into cobertura_tmp_resumen from cobertura_tmp group by lat,lng;

--    drop table if exists cobertura_tmp;

END;
$$;

The same script runs remotely and smoothly from Mac OSX using Postico Version 1.3.2 (2318).

QUESTION: Why isn't it finding the sql variable that is in the declare section?


texts: How to return value for key from a jsonb column?

texts:
I've successfully created the below table on 9.5.1.0:

  CREATE TABLE json_test (
    id serial primary key,
    data jsonb
  );

and successfully inserted this record:

  INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}')

and I am successfull in returning the entire record that I inserted via:

  SELECT * FROM json_test WHERE data::jsonb ? '1';

but how do I return the value associated with '1', i.e. "BELOW" ?


texts: Database is not accessible after 'taking it offline' process failed

texts:
I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying

'an exception occured while executing a transact SQL statement or batch

-> ALTER DATABASE failed because a lock could not be placed on database 'myDB'. Try again later.
    ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)'

Now if I try and access the DB it says it is not accessible. What can I do to make my DB accessible again?

My aim was to detach the DB, relocate its secondary database file to a new drive and reattach it (simply because of space issues).


texts: Identical query, tables, but different EXPLAIN and performance

texts:
I have two tables levels and users_favorites

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| user_id            | int(10)      | NO   | MUL | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(3,2) | NO   |     | 2.50    |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | YES  |     | NULL    |       |
| playable_character | int(2)       | NO   | MUL | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| user_id  | int(8) | NO   | PRI | NULL    |       |
| level_id | int(8) | NO   | PRI | NULL    |       |
+----------+--------+------+-----+---------+-------+

I have my local dev environment and the production servers. This query:

SELECT id, level_name, date_published, rating
FROM levels
WHERE id IN (SELECT level_id FROM users_favorites WHERE user_id = 2);

runs very fast locally (around 0.0x seconds) and very slow on production (~15 seconds). The EXPLAIN's are different. On local:

id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  users_favorites ref uniq_user_level,idx_user    idx_user    4   const   21  "Using index"
1   SIMPLE  levels  eq_ref  PRIMARY PRIMARY 4   users_favorites.level_id    1   "Using where"

And on production:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY levels  ALL NULL    NULL    NULL    NULL    3368988 "Using where"
2   "DEPENDENT SUBQUERY"    users_favorites eq_ref  uniq_user_level,idx_user    uniq_user_level 8   const,func  1   "Using index"

I know the data is the the same because it was imported and exported from the same schema. I've run OPTIMIZE and made sure the indexes are the same, tried forcing the indexes. Nothing worked.

The only difference I can spot is the version of MySQL: locally it's 5.6.10, on production it's 5.5.34-log. If that's the reason, I'll upgrade, but I'm wondering if there could some other reason? Or way to phrase the query so it always reduces by the subquery first, as it does locally: 21 rows instead of 3368988?

TIA


texts: Administrator can't view tables ("server refused to connect")

texts:
I'm quite experienced with MSSQL and MySQL/MariaDB, but I'm a newbie re PostgreSQL.
I got everything to work, except for the odd detail that the admin user which I set up during installation can't "view/edit" tables. The error displayed is:

domain.com refused to connect

Here's what works
I installed PostgreSQL 12.3 and pgAdmin4 both on a Ubuntu 20.04 LTS server (Azure VM, CLI only, no GUI). Everything works, I can log in from a browser (domain.com/pgadmin4), and create databases and tables.
This doesn't seem to work
However, when I right-click on a table (View/edit data -> first 100 rows), then I get the error message:

domain.com refused to connect.

Equally strange observation
Also, on another machine, I use Pentaho Data Integration for ETL purposes. From there, I can connect, create tables (via the automatic SQL code it generates), and I can both write and read tables, not a problem at all.
Problem
My only issue is that the admin user in pgAdmin4 cannot read tables (despite the "Administrator" role). I also turned off ufw (the Ubuntu Firewall), and any IP restrictions in the Azure account (which is effectively a second firewall). I also checked the logfile postgresql-12-main.log, but I didn't see any related entries.
I'd really appreciate if someone could give me an idea how to troubleshoot that.


texts: Migrating log files - should I copy file/directory permissions?

texts:
I'm planning to move the log files for the databases ReportServer, ReportServerTempDB, tempDB, and msdb to a new drive.
But it seems that permissions aren´t copied with the directories and files; should I bother to move them over? (Or is that overkill?)


texts: How to update one table based on another table's values on the fly?

texts:
I have a table in the name of ips as below:

CREATE TABLE `ips` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `begin_ip_num` int(11) unsigned DEFAULT NULL,
 `end_ip_num` int(11) unsigned DEFAULT NULL,
 `iso` varchar(3) DEFAULT NULL,
 `country` varchar(150) DEFAULT NULL
) ENGINE=InnoDB

Lets assume I have a countryid field on this table from country table which is as below:

CREATE TABLE `country` (
 `countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `ordering` smallint(5) unsigned NOT NULL DEFAULT '0',
 `iso` char(2) NOT NULL,
 PRIMARY KEY (`countryid`)
) ENGINE=InnoDB

There is about 100,000 records in ips table. Is there any query for the following scenario:
Check if ips.iso is equal to country.iso, if it's equal then add country.coutryid to that record. I couldn't think of any way to do it. Do you have any idea how to do that?


texts: Database Design for Pharmacy store with wholesale and retial

texts:
I have designed a DB ( Inventory Management ) for Pharmacy Store  which will sell medicine both wholesale and retail. I store each units with its conversion for medicine in a separate fields for retail selling.

When I generate the inventory movement report or sum transactions, I need to calculate the each ordered units and it makes my logic very complex. Is there any better approach for this ?
Thanks.


texts: fulltext_stoplists to database_principals issue

texts:
When I run a DBCC check against the database, it informs me that there is an issue with the sys.fulltext_stoplists object.  It appears that the principal_id in stoplists does not have a relationship with database_principals:

What would be the best way to address the issue? It is difficult to modify data in system tables.


texts: Can't install mongodb-enterprise. Error: nothing provides libsasl2.so.2()(64bit)

texts:
I am trying to install mongodb-enterprise on fedora 25, as guided here. I created the repository (as suggested in step 1), but when I run the command sudo yum install -y mongodb-enterprise

I get this error:

  Error: nothing provides libsasl2.so.2()(64bit) needed by
  mongodb-enterprise-tools-3.4.0-1.amzn1.x86_64

and when I try to install cyrus-sasl-lib it says Dependencies resolved.
Nothing to do. , I found the command from here, which provides the library libsasl2.so.2. It seems I am stuck.

PS: I've already tried installing from a Tarball but that also doesn't seem to be working for me. And I have to use mongo-shell version 3.4 only.


texts: Bypass truncated data type error in `CREATE TABLE AS`

texts:
I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list one at a time and then rebuild the nested sets at certain time intervals).

However, I've hit a snag with this bit:

SortPath =  CAST(
                    CAST(anchor.EmployeeID AS BINARY(4))
                AS VARBINARY(4000)) --Up to 1000 levels deep.

I've switched it to this:

CAST(CAST(anchor.EmployeeID AS BINARY(4)) AS BINARY(4000)) AS sort_path ## Also with 8000

I also tried CAST(anchor.EmployeeID AS Binary(4)) AS sort_path

All approaches have thus far yielded a variant of this error message:

Truncated incorrect DOUBLE value: '1\x00\x00\x00'

EDIT

The full sql statement being run is this:

##===== Conditionally drop Temp tables to make reruns easy
DROP TABLE IF EXISTS hierarchy;

##===== Build the new table on-the-fly including some place holders
CREATE TABLE hierarchy AS

WITH RECURSIVE cteBuildPath AS
( ##=== This is the "anchor" part of the recursive CTE.
     ## The only thing it does is load the Root Node.
 SELECT anchor.tree_user_id,
        anchor.upline_id,
        1 AS hlevel,
        CAST(anchor.tree_user_id AS BINARY(4)) AS sort_path ##Up to 1000 levels deep.
  FROM example.tree AS anchor
  WHERE upline_id IS NULL ##Only the Root Node has a NULL ManagerID
  UNION ALL
 ##==== This is the "recursive" part of the CTE that adds 1 for each level
     ## and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.tree_user_id,
        recur.upline_id,
        cte.HLevel + 1,
        cte.sort_path + CAST(recur.tree_user_id AS BINARY(4))
   FROM example.tree AS recur
  INNER JOIN cteBuildPath AS cte
          ON cte.tree_user_id = recur.upline_id
) ##=== This final SELECT/INTO creates the Node # in the same order as a
     ## push-stack would. It also creates the final table with some
     ## "reserved" columns on the fly. We'll leave the SortPath column in
     ## place because we're still going to need it later.
     ## The ISNULLs make NOT NULL columns.
 SELECT IFNULL(sorted.tree_user_id,0) AS tree_user_id,
        sorted.upline_id,
        IFNULL(sorted.hlevel,0) AS hlevel,
      #  IFNULL(CAST(0 AS INT),0) AS left_bower, ##Place holder
      #  IFNULL(CAST(0 AS INT),0) AS right_bower, ##Place holder
      (@n := @n + 1) AS node_number,
      #  IFNULL(CAST(0 AS INT),0) AS node_count, ##Place holder
        IFNULL(sorted.sort_path,sorted.sort_path) AS sort_path
   FROM cteBuildPath AS sorted
   JOIN (SELECT @n := 0) n ON 1=1
   #ORDER BY sorted.sort_path
 #OPTION (MAXRECURSION 100) ##Change this IF necessary
;

What is the correct way to duplicate the desired logic in MariaDB 10.2?


texts:
We currently have an SSIS installation on the same server as SQL Server. This SQL Server installation is strictly to run SQL Server Agent to schedule the jobs that execute the SSIS packages deployed to this server.  No user DBs, just MSDB and SSISDB, and then a small DB (<100mb) for the DBAs to store certain stored procs and tools to check status, etc.
We have 128GB of memory for the server, and half (64GB) of the memory is dedicated to SQL Server.  The EDW database that we are pushing the data to in SSIS is located on a different server entirely.
Is this set-up correct?  Is it correct to have Integration Services run on the same server that you are scheduling the packages through SQL Server agent on?   I ask because we are running into memory issues, and the DBAs are suggesting that we increase the memory allotted to SQL Server.   Should we give more memory to SQL Server?


texts: Optimising pagination with large dataset and offset

texts:
Suppose that I have 50 million record of user.
And the business requirement is to allow the admin to select any pageNumber and any pageSize to retrieve the user record

Of course, it will not be a problem if admin key in page 1 with pageSize of 10.
But let say admin key in page 1 million with pageSize of 10, the querying time is a problem.

Is there any solution to this as a simple offset and limit solution with indexing alone wouldn't solve the problem??? I am thinking that how could this be addressed without changing the business requirement.

Simplify:
- I need a stored proc that accept pageSize and pageNumber to get user record
- I have 50 million user record
- The user id in my database is guid (not incremental based on created date)
- Query result should be order by User Created datetime
- Expectation: Querying time of pageNumber 1 and pageNumber 1 million should not be much different, or at the very least it should be acceptable time to return result (eg: less than 5 seconds)

CREATE PROCEDURE [dbo].[usp_AdminGetUserList] (
     @PageSize int = 10,
     @PageNumber int = 1
)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT
        *
    FROM [dbo].[User]
    ORDER BY [JoinDT]
    OFFSET @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);

END

Note:

I know that there is solution which use the last primary key (incremental userid) in this case instead of using offset, but such solution would not fit the business requirement as business requirement allow user to key in pageNumber(offset). In other word, solution like this will not work for my case.

SELECT user_id, name
FROM users
WHERE user_id > 51 234 123
ORDER BY user_id ASC
LIMIT 10 000;


texts:
I'm trying to set up transactional replication between two SQL Server 2008 R2 databases and, no matter what I try, it just doesn't seem to work, and I'm not sure where to look for further information about what may be going wrong.

I've created the publication and subscription by running through the wizards on both servers, and everything appears to be up and running - I get successful messages on the Publisher that it's created the publication and added the articles to the publication etc. I also subscribe the subscription database successfully to this publication. The replication monitor shows everything OK - but the synchronisation status of both the publication and the subscription always show as "No replicated transactions available" even when I manually add / modify data in the publication database.

I've tried deleting the replication data from both servers, dropping and recreating the databases from backups, reinitializing the subscription database etc all with no joy.

Any advice on where to look for some more info on what may be going on would be gratefully appreciated.


texts: What can I do to make this mysql script run faster (or run)?

texts:
I have a user table ldap_karen that has all employee data including sup_id (supervisor id) and the employee's job role.

I also I have a supervisor table that has all the unique sup_ids.

Currently I have:

SELECT  ldap_sups.sup_id, COUNT(ldap_karen.uid) as team_nums,
        COUNT(sa_title) as title_nums,
        ( SELECT  sa_title
            FROM  ldap_karen
            WHERE  ldap_sups.sup_id=ldap_karen.sup_id
            group by  sa_title
            order by  count(ldap_karen.sa_title) desc
            limit  1
        ) as sup_role,
        ldap_karen.sa_title, ldap_karen.regionname
    FROM  ldap_sups
    JOIN  ldap_karen ON ldap_sups.sup_id = ldap_karen.sup_id
    GROUP BY  sup_id

So this gives me the supervisor ID, then number of team members, number of job titles (not all employees have job titles), sup_role - this is what is not working, sa_title (which just lists the job title of the last person in the list), and region.

I would like sup_role to display the "most" job title. So whatever job titles comes up the most for the employee under the supervisor is the sup_role.

Note: Right now my query just runs - due to an issue with the "sup_role" query. Nothing outputs.  I get hit with maxing out execution time.  I have 80K employees with 12K supervisors.

Addendum:  I did get this down to a little under 10 mins and it runs.  However I would like to know if there is a way to do this faster as I couldn't run this on our production web server as it might cause mysql crashes.  Also to answer Rick's questions - 8GB of RAM and InnoDB.

Another note:  Allocated more memory to mysql and still around 500 secs!


texts: UPSERT statement never completes, infinite loop?

texts:
Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would be possible in this case for the insert and update statements to be mismatched and therefore enter an infinite loop):

CREATE FUNCTION upsert(sql_insert TEXT, sql_update TEXT) RETURNS VOID AS
$$
DECLARE
    i integer := 0;
BEGIN
    LOOP
        -- terminate if loop reaches 2 runs
        IF i >= 2 THEN
            RETURN;
        END  IF;
        BEGIN
            -- try update
            EXECUTE sql_update;
            -- terminate if update successful
            IF found THEN
                RETURN;
            END IF;
            BEGIN
                -- try insert
                EXECUTE sql_insert;
                RETURN;
            -- insert violated unique constraint, try loop again
            EXCEPTION WHEN unique_violation THEN
            END;
        END;
        i := i+1;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a particularly big table with around 11,000 rows.

However, this sample input never finishes running:

SELECT upsert(
$$
INSERT INTO accounts
VALUES (
    '0012000000wa7WzAAI', 'Random company', NULL,
    'Tier 2', 'Joe BLoggs', 'Joe Bloggs', 'US',
    TIMESTAMP '2012-12-14 00:00:00', NULL, 'Ex-Customer')
$$,
$$
UPDATE accounts
SET account_name='Random Companyh', country=NULL, account_tier='Tier 2',
    account_owner='Joe Bloggs', sales_person='Joe Bloggs',
    office_managed='US', date_first_opportunity=TIMESTAMP '2012-12-14 00:00:00',
    industry=NULL, account_type='Ex-Customer'
WHERE account_id='0012000000wa7WzAAI'
$$
)

I can't work out why this isn't working, any help would be appreciated.


texts:
I intend to create a wiki webpage, but it has some few more limitations than regular wikis. The content is completely user-editable, but the structure of the page is determined previously. To make things simpler, let's say that they have a structure of a (title, author, date, chapter1, chapter2,...,chapter10).

So some of them may be empty (like last chapters). I would definitely like the ability to search into it and, if possible, I'd like it to be expandible (to add more fields). The page is edited online with my own editor, so I have full control of the $_POST fields, and each one is in a different variable. So I was thinking about this 2 possible designs:

The page is first formatted using XML elements (through PHP) and then inserted into a single text cell in the table. This allows to make backups easier, as well as it's flexible for expansion and does not waste many space resources. The table would also include a modified date and last editor.
The mysql table has many columns and each one has a different field. This is not flexible, nor resource-savvy, but it's much easier to operate with.

I was thinking about learning the PHP functions for reading and writing XML and then work with the first option. But I'm new in MYSQL databases (and db design), so I was wondering if this is a good practice before I get on it or there are other ways.

So, what method is considered to be a "good practice", 1st, 2nd or different one?


texts:
I'm trying to backup SQL Server database to upload it to my database server. I am using Microsoft SQL Server Management Studio, Database -> Tasks -> Back Up...
And I got the following error:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Any idea of what's happening?


texts:
I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code.
Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
        event_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
BEGIN
  -- TODO Loop until TG_ARGV[0] empty
INSERT INTO events (measurement_id, event_index_start, event_index_end)
SELECT NEW.measurement_id, TG_ARGV[0]::int[], TG_ARGV[1]::int[];
  -- END TODO
RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}");

which I can start by

INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);

I know that the mistake is in casting here TG_ARGV[0]::int[] into intarray which I would like loop through until the intarray is empty.
There may exists better way to do such loop inserts.

ErwinBrandstetter's code output

Code

DROP TABLE IF EXISTS measurements, events, file_headers;

CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
        event_id SERIAL PRIMARY KEY NOT NULL,
        measurement_id INTEGER NOT NULL,
        event_index_start INTEGER NOT NULL,
        event_index_end INTEGER NOT NULL
);

DROP TRIGGER IF EXISTS insaft_ids ON measurements;
DROP FUNCTION IF EXISTS insaft_function();

CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
DECLARE
   m int[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV::int[]
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end)
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;
   RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{201,300}}');

Running

sudo -u postgres psql detector -c "INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777);"

getting

ERROR:  invalid input syntax for integer: "{{101,201},{201,300}}"
CONTEXT:  PL/pgSQL function insaft_function() line 5 at FOREACH over array

How can you cast from ARGV[] which is text to int array in PostgreSQL?


texts:
I'm troubleshooting a MySQL no-start on CentOS 7 VM. It is a bit unusual to see this since we have not made any configuration changes. The manual start command was taken from systemd logging. The error is the age old:

# /usr/bin/mysqld_safe --basedir=/usr
181221 17:42:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
181221 17:42:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe_helper: Can't create/write to file '/var/log/mariadb/mariadb.log' (Errcode: 13)

According to Red Hat Bugzilla – Bug 1061045, mysql:mysql needs the appropriate permissions, which we seem to have:

[root@ftpit w]# ls -Al /var/log
total 52764
...
drwxrwx---  2 mysql mysql      4096 Aug 16 11:05 mariadb

And:

[root@ftpit w]# ls -Al /var/log/mariadb/
total 99852
-rw-rw---- 1 mysql mysql 102138776 Dec 21 17:16 mariadb.log

And:

[root@ftpit w]# grep mysql /etc/passwd
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

I also tried g-w as shown in the bug report.

Where should I look next for this error? Or how do I fix it?


texts:
I am trying to port a database where there is a table with a column of type unknown. pg_restore raised the error

  pg_restore: [archiver (db)] could not execute query: ERROR:  column "color" has pseudo-type unknown`.

I don't understand why such tables can exist given that the doc says

  A pseudo-type cannot be used as a column data type

However, I want to keep the database as it is and do not do any modifications. Which error code should I specify to catch this exception?


texts: Meaning of SERVERPROPERTY ('ResourceLastUpdateDateTime')

texts:
I have researched a while, a lot of articles name it and tell us to use it - but none really explains the meaning of the date and WHEN exactly it changes. Does anyone know?

Samples for articles not really explaining the meaning of this property:

https://msdn.microsoft.com/en-us/library/ms190940.aspx
http://www.mytechmantra.com/LearnSQLServer/How-to-Identify-the-Location-of-Resource-Database-in-SQL-Server/

Edit: The backstory behind the question simply does not exist. I just want to know what event forces this information to get a new timestamp. What makes the resource db getting updated?


texts:
Here are my tables;

CREATE TABLE `borclular` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tc` char(11) COLLATE utf8_turkish_ci NOT NULL,
  `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tc` (`tc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

 CREATE TABLE `dosyalar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kurum_id` int(11) NOT NULL,
  `borclu_id` int(11) NOT NULL,
  `hizmetno` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kurum_id_index` (`kurum_id`),
  KEY `borclu_id_index` (`borclu_id`),
  CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`),
  CONSTRAINT `kurum_id_foreign` FOREIGN KEY (`kurum_id`) REFERENCES `kurumlar` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

When I delete a row from borculular, I want related row from dosyalar to be also deleted.However, when I try to delete a row from borclular, I get the following error;

Cannot delete or update a parent row: a foreign key constraint fails (`iletisim`.`dosyalar`, CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`))


texts:
work0.psg.com:/var/db/mysql/wordpress# mysqldump -p --databases wordpress > dump.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Got error: 1812: Tablespace is missing for table `wordpress`.`wp_termmeta`. when using LOCK TABLES

but, while it looks to be a .ibd issue, for which there are a lot of (confusing to me, lacking mysql fu) recipies,
work0.psg.com:/var/db/mysql/wordpress# ls -l *termmeta*
-rw-rw----  1 mysql  mysql    8682 Jan  9  2016 muddy_termmeta.frm
-rw-rw----  1 mysql  mysql  131072 Jan  9  2016 muddy_termmeta.ibd
-rw-rw----  1 mysql  mysql    8682 Dec 17  2015 rtech_termmeta.frm
-rw-rw----  1 mysql  mysql  131072 Dec 17  2015 rtech_termmeta.ibd
-rw-rw----  1 mysql  mysql    8682 Dec 17  2015 wp_termmeta.frm

i am confused because it looks more to be missing MYD/MYI then .ibd.  but i am more of a computer scientist than DB admin; so in over my head.
...
-rw-rw----  1 mysql  mysql     1056 Sep  7 18:50 wp_term_taxonomy.MYD
-rw-rw----  1 mysql  mysql     4096 Sep 16 04:25 wp_term_taxonomy.MYI
-rw-rw----  1 mysql  mysql     8768 Sep  3  2009 wp_term_taxonomy.frm
-rw-rw----  1 mysql  mysql     8682 Dec 17  2015 wp_termmeta.frm
-rw-rw----  1 mysql  mysql      540 Dec 17  2015 wp_terms.MYD
-rw-rw----  1 mysql  mysql     4096 Jan 16  2016 wp_terms.MYI
-rw-rw----  1 mysql  mysql     8668 Dec 17  2015 wp_terms.frm

but i see no discussions of that.
mysql  Ver 14.14 Distrib 5.7.31, for FreeBSD12.1 (i386) using  EditLine wrapper


texts: How to setup replication(Master/slave) in MySQL 5.5.20?

texts:
I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine.

I tried following this tutorial (I used instructions given on this website.) but it is not working. I found that after doing all this I couldn't ... (((Please update this part with your experiences)))

Could you please suggest me a tested and an efficient way to do this in MySQL 5.5.20?


texts: Always On Listener Behavior - DBs removed from group

texts:
We have multiple multi-site always on failover cluster instances in our env. We have an enforced internet outage being imposed on us and at the time the link between the data centers will be down for up to 6 hours...

Our plan is to remove the Databases from the AGs on the primary instances, but leaving the listener in place so the applications should still connect to the DBs via the VIP/DNS.

We have tested this and did not encounter any issues.

However we are struggling to find any information to say that this approach is best practice. We cannot suspend movement as the outage could be long and MS recommend movement is suspended for a short period only.

Anyone else had to deal with this situation? If yes - is our approach feasible?

Once the outage is complete we will disable log backups on the Primary nodes, delete the non-synched DBs on the secondary nodes and then add them all back in on the primary.

Any thoughts or input would be greatly appreciated.


texts: When I run show variables like 'have_partitioning'; it return empty record. When I run show plugins I got this result  partition | ACTIVE | STORAGE ENGINE|NULL    | GPL| My MySQL version is 5.6.29 and I'm running it in CentOS. What I have to do to get Yes when I run 'show variables like 'have_partitinoing


texts: How do I configure a MySQL Cluster with a redundant mysqld host?

texts:
this is my first post here, so go easy on me.

I'm trying to rebuild my data architecture (which is currently a set of one primary MySQL server and a redundant fallback; the tables are primarily InnoDB for transactional features). This has worked well in the past, but with recent increases in traffic, it's not holding up very well.

At first I was considering adding a 'memory' cache table to speed things up, but I'd prefer something with transactions. So I've been looking into setting up a MySQL Cluster, but the documentation is a lot to take in, and I've now gotten myself very confused. Here's what I hope to accomplish:

I'd still like to have two mysqld machines, but I'd like to add two or three ndb nodes, and have the mysqld machines double as ndm-mgmd.

Is this a practical setup? How do I go about configuring this? If anyone knows of helpful examples or guides, I'd be pleased as punch (I've been searching, and each guide I stumble upon is twice as confusing as the last).


texts: Are there reasons why I should not set my db owner to [sa]?

texts:
Yesterday I asked this question regarding changing the dbo of multiple databases that I have.  The change makes sense, but I want to be clear.

Is there any, good reason or circumstance why I should not set the dbo of a database to [sa]?


texts:
OK, total newb question.

What is "SAP on Oracle", exactly.  This link is talking about how SAP can run on Oracle (I think).

I'm not understanding how this is working.

My understanding:

SAP is a company that provides ERP
Oracle is a database system.

I thought they were competitors!  So, is SAP on Oracle running the ERP system on Oracle?  What is running here and what would a DBA be supporting in this scenario?  (Or would this even be DBA territory?)

I'm asking because I've applied for a job that prefers "SAP" experience and I ran into this "SAP on Oracle" in my googling.


texts:
I inherited a 5 node postgres cluster running postgres 8.4 and slony 1.2.21. We are in the process of migrating the application to all new code and have not wanted to do very little maintenance on the cluster. Yesterday we decided to take down two nodes out of the cluster which were not being used. We used the slonik script to DROP NODE for the two nodes in the cluster. This seemed to work correctly and we shut down the nodes today. However I noticed this morning that our master database where the collect writes is not replicating the changes to the rest of the servers. I have tried everything I can think of but it appears but nothing seems to work.

When I run a query to collect the status I see that events are not being acknowleged since yesterday. The st_last_received has not changed at all.

 st_origin | st_received | st_last_event |      st_last_event_ts      | st_last_received |    st_last_received_ts     | st_last_received_event_ts  | st_lag_num_events |              now
-----------+-------------+---------------+----------------------------+------------------+----------------------------+----------------------------+-------------------+-------------------------------
        25 |          24 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-29 12:51:45.396619 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05
        25 |          27 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-28 11:11:49.203193 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05
        25 |          26 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-28 11:11:50.253235 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05

I first restarted the slony daemons on all the nodes and have subsequently done this multiple times.  I have set debugging to level 4 for debug logging and have combed through them without finding a single issue.

I have looked through all the .sl_ tables for anything that might tell me why it is not working.

Our configuration is as follows for the important replication set.

select * from _ads.sl_set;
 set_id | set_origin | set_locked |   set_comment
--------+------------+------------+-----------------
      1 |         25 |            |  mgt tables

select * from _ads.sl_subscribe ;
 sub_set | sub_provider | sub_receiver | sub_forward | sub_active
---------+--------------+--------------+-------------+------------
       1 |           25 |           26 | t           | t
       1 |           25 |           27 | t           | t
       2 |           25 |           27 | t           | t
       1 |           25 |           24 | t           | t

select * from _ads.sl_listen ;
 li_origin | li_provider | li_receiver
-----------+-------------+-------------
        24 |          24 |          25
        26 |          26 |          25
        27 |          27 |          25
        27 |          25 |          26
        26 |          25 |          27
        27 |          25 |          24
        24 |          25 |          27
        24 |          25 |          26
        26 |          25 |          24
        26 |          24 |          25
        27 |          26 |          25
        26 |          27 |          25
        24 |          26 |          25
        27 |          24 |          25
        24 |          27 |          25
        25 |          25 |          24
        25 |          25 |          26
        25 |          25 |          27

Any advice assistance, or an idea on where to look would be greatly appreciated. I am in full on panic mode now.


texts: Missing data in specific tables post log backup and shrink

texts:
We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their .ldf files were growing. We took a log backup and tried to shrink the log file. Not sure if the shrink actually ran, the wizard closed in 2-3 secs and the .ldf file size didn't reduce.

Later that day it has been found that data as far as two years old have been lost in specific tables in that DB. Impacted tables are less than 10, There are around 500 tables in the DB and more than 100 of them involve daily transactions.

Please clarify if the log backup and shrink has any part to play in this?
If yes, how could this have been avoided and what else was supposed to be done to keep the .ldf in check?


texts: Workaround for the limit of the number of missing indexes suggested by SQL Server's DMVs

texts:
In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to telemetry, in order to do some post analysis on how well the queries sent to these orgDbs are doing and possibly do some optimizations.

Sounds straightforward, right? The problem however, is that there is a max limit of 500 for the number of missing indexes that DMVs can report in a single SQL Server whereas we expect around 20 missing indexes per OrgDb (~ 5000 in total).

Can anyone think of a workaround for this limit? One solution I initially thought of, was to delete the DMVs' missing index tables:

sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group

after each update, but turns out those tables cannot be modified:

Error:Ad hoc updates to system catalogs are not allowed.


texts: Logging end-user to SQL-Server audit log in a N-tier application

texts:
In a traditional application architecture with a web-frontend, .NET webapp middleware and a SQL-Server backend, I wish to be able to see in the SQL-Server event log what end-user (person interfacing the web-frontend) did what queries.
The .NET webapp knows the authenticated user but will connect to Sql-Server using its own credentials (service account / SQL Login). I don't want to use impersonation (connecting to SQL in the context of the end user).
One way to do this is to log connection-id on connection-open and connection-close, together with the user, to the application event log and later correlate logs from the two systems. It would however be simpler if the end-user-id could be somehow be added to the SQL-Server log.
I've looked at setting a SESSION_CONTEXT variable on connection-open variable but there seems to be no way to include these in the audit log.


texts:
Approximately 1 time per week my MySQL server is shutting down.

This is what the log looks like:

2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
2016-03-18 13:46:25 13166 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-03-18 13:46:25 13166 [Note] Plugin 'FEDERATED' is disabled.
2016-03-18 13:46:25 13166 [ERROR] Function 'innodb' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'federated' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'blackhole' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'archive' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2016-03-18 13:46:25 13166 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-18 13:46:25 13166 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-18 13:46:25 13166 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-18 13:46:25 13166 [Note] InnoDB: Memory barrier is not used
2016-03-18 13:46:25 13166 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-18 13:46:25 13166 [Note] InnoDB: Using Linux native AIO
2016-03-18 13:46:25 13166 [Note] InnoDB: Using CPU crc32 instructions
2016-03-18 13:46:25 13166 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12
2016-03-18 13:46:25 13166 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' init function returned error.
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-03-18 13:46:25 13166 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-03-18 13:46:25 13166 [ERROR] Aborting
2016-03-18 13:46:25 13166 [Note] Binlog end
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'partition'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-03-18 13:46:25 13166 [Note] /usr/sbin/mysqld: Shutdown complete

160318 13:46:25 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What do you think? Maybe there is not enough memory?

This is running on a Virtual Private Server running Ubuntu 15.10 with 512MB of memory.

Now I can't even restart my MySQL server.  My my.cnf is empty, What can I do?


texts: Oracle 12c Installation (Grid) on RHEL 7.1 CLSRSC-214: Failed to start 'ohasd'

texts:
root.sh script execution failing with following error please advice

./root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/12.1.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node lrma086 successfully pinned.
2015/10/22 01:39:44 CLSRSC-330: Adding Clusterware entries to file '/etc/inittab'

2015/10/22 01:41:50 CLSRSC-214: Failed to start 'ohasd'

Failed to start the Clusterware. Last 20 lines of the alert log follow:
2015-10-22 01:39:34.970:
[client(21726)]CRS-2101:The OLR was formatted using version 4.
2015-10-22 01:39:39.368:
[client(21753)]CRS-1001:The OCR was formatted using version 4.

2015/10/22 01:41:50 CLSRSC-318: Failed to start Oracle ohasd service

Died at /u01/app/grid/product/12.1.0/grid/crs/install/crsinstall.pm line 2985.
The command '/u01/app/grid/product/12.1.0/grid/perl/bin/perl -I/u01/app/grid/product/12.1.0/grid/perl/lib -I/u01/app/grid/product/12.1.0/grid/crs/install /u01/app/grid/product/12.1.0/grid/crs/install/roothas.pl ' execution failed