texts:
I wonder if there is any way to change localhost in MariaDB.
For example, while creating a user in database, it shows
'user'@'localhost'
I would like to know how to change localhost to any other hostname.
For example, I will be able to create a user in database like the following:
 'user'@'mydomain.com'
By the way, I'm very new in MariaDB and SQL server stuff.
Thank you in advance!


texts: Oracle - Sync Sequence nextval using CONNECT - side effects?

texts:
DB
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Action
After a data migration - from an old table to a new table - we created a new sequence and executed this command to sync it's next value to be equal to table maximum id.
SQL
-- SYNC SEQUENCE WITH TABLE
SELECT level, {SEQUENCE_NAME}.NEXTVAL
FROM dual
CONNECT BY level <= (
 SELECT MAX(ID)
 FROM {TABLE}
);

Question

Is there any side effect on calling this SQL - since the keyword CONNECT is used to make connections to DB ?
Is there a more efficient way to do such task?

Useful Links
Oracle - CONNECT Keywork - Reference


texts: Why is my SQL Server reporting CLR_AUTO_EVENT waits when I don't even use SQL CLR?

texts:
When I run this query, I see high wait time for CLR_AUTO_EVENT:

SELECT
    *
FROM
    sys.dm_os_wait_stats
WHERE
    wait_type IN
    (
        'CLR_AUTO_EVENT',
        'CLR_CRST',
        'CLR_JOIN',
        'CLR_MANUAL_EVENT',
        'CLR_MEMORY_SPY',
        'CLR_MONITOR',
        'CLR_RWLOCK_READER',
        'CLR_RWLOCK_WRITER',
        'CLR_SEMAPHORE',
        'CLR_TASK_START',
        'CLRHOST_STATE_ACCESS',
        'ASSEMBLY_LOAD',
        'FS_GARBAGE_COLLECTOR_SHUTDOWN',
        'SQLCLR_APPDOMAIN',
        'SQLCLR_ASSEMBLY',
        'SQLCLR_DEADLOCK_DETECTION',
        'SQLCLR_QUANTUM_PUNISHMENT'
    )
ORDER BY
    wait_time_ms DESC,
    wait_type ASC;

In a related question What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?
, I read that it appears when running .NET managed code within SQL Server.

However, I don't think I'm using any of that. When I execute EXEC sp_configure 'clr enabled', I see:

It looks to me like it's not even enabled. Can anybody please explain what's going on?

PS: I'm running SQL Server 2017 Developer Edition (14.0.3026.27).


texts:
I am given the schema:
BOOKING (hotelNo, guestNo, dateFrom, dateTo)
GUEST (guestNo, guestName, guestAddress)
I an asked to formulate the query for:
List the guest numbers associated with the first name ‘Peter’ having made bookings with unknown dateTo without using an explicit or implicit joins.
My attempted query is:
  (select guestNo from GUEST where guestName LIKE 'Peter')
  INTERSECT ALL
  (SELECT guestNo FROM BOOKING WHERE dateTo IS NULL);

But this does not return duplicates only at most 1 entry per guestNo, as shown in fiddle where returned values should be (1,1) rather than (1). I thought about using UNION but then this will return guestNo's if dateTo is NULL or name is Peter, i.e. both conditions may not necessarily hold.
Database fiddle: https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17
Do you guys have any suggestions?


texts:
I came across with a strange issue in postgres. I have two databases create in two different periods.
Both are running in the same PostrgreSql 9.6 version in the same machine. When I run the query SELECT now() + 30 in database 1 -> Its working properly.
The same query I am copy paste in the second database, its giving error:  operator does not exist: timestamp with time zone + integer in PostgreSql
Can some one guide me on this.


texts:
so I tried to make a data warehouse OLAP Cube with Talend Open Studio. In this OLAP Cube process, i used 3 table as dimentional table: cleaned_Student3, cleaned_TeachingLearningProcess, cleaned_Province. But I have error in the process.
This is my tPaloCube compontent : image
Can you please tell me where did I make a mistake? Sorry for my broken english and thankyou all in advance!
I have error like this:
[statistics] connecting to socket on port 3622
[statistics] connected
Exception in component tPaloCube_1 (DWH_OLAP_Akademik3)
org.talend.jpalo.paloexception: org.talend.jpalo.paloexception
    at org.talend.jpalo.paloconnection.<init>(paloconnection.java:71)
    at org.talend.jpalo.palo.connect(palo.java:14)
    at local_project.dwh_olap_akademik3_0_1.DWH_OLAP_Akademik3.tPaloCube_1Process(DWH_OLAP_Akademik3.java:322)
    at local_project.dwh_olap_akademik3_0_1.DWH_OLAP_Akademik3.runJobInTOS(DWH_OLAP_Akademik3.java:624)
    at local_project.dwh_olap_akademik3_0_1.DWH_OLAP_Akademik3.main(DWH_OLAP_Akademik3.java:473)
[statistics] disconnected


texts: What is the difference between NORECOVERY and RECOVERY when restoring database?

texts:
Can anyone explain the main differences?

Any help appreciated.


texts: How to Add A User To A Database In Standby / Read-Only Mode In Sql Server

texts: I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?


texts: How do I connect to mysql without having to type an empty password?

texts:
I just installed MySQL, and by default the root user has no password. If I connect with this command:

mysql --port=3307 --host=127.0.0.1 -uroot -p

It asks me to enter a password, I just hit return, and it connects me. However, I want to make this part of a script, by piping a statement to it non-interactively, like this:

echo "$statement" | mysql --port=3307 --host=127.0.0.1 -uroot -p

But, when I actually do that, it still asks me for the password. How do I make it NOT ask for the password, and just not use one?

I tried removing the -p, and I get this message:

$ mysql --port=3307 --host=127.0.0.1 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I would expect there to be some sort of --nopassword argument, but I can't find one.


texts: What is the most convenient indexing strategy for two foreign key constraints?

texts:
Which of the two following options is more convenient for indexing the  relevant foreign keys?

Option A:

Create Table table3(
  t3_id int not null auto_increment,
  t1_id int not null,
  t2_id int not null,
  primary key (t3_id),
  index IX_index (t1_id, t2_id), // this is my concern
  constraint FK_t1 foreign key (t1_id)
  reference table1(t1_id),
  constraint FK_t2 foreign key (t2_id)
  reference table2(t2_id));

Option B:

Create Table table3(
  t3_id int not null auto_increment,
  t1_id int not null,
  t2_id int not null,
  primary key (t3_id),
  index IX_t1 (t1_id), //this is my concern
  index IX_t2 (t2_id), //and this
  constraint FK_t1 foreign key (t1_id)
  reference table1(t1_id),
  constraint FK_t2 foreign key (t2_id)
  reference table2(t2_id));

I'm using InnoDB. I don't have a broad understanding how a btree index works but, as far as I know, the first table will save the indexes on a single area while the second will not (correct me if I am wrong).

What are the pros and cons of defining a single- or a multi-column index?


texts: Oracle 11g Rman restore with another database name from backup in windows platform

texts:
Can anyone share the proper method of RMAN restore to another server with another database name in oracle 11g(Windows platform). I'm totally confused with all the RMAN restore methods which I've found. I've taken the backup using the command

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Please confirm whether is it required to take control file backup separately.

Any help will be appreciated.


texts: What to do when the active log directory is full?

texts:
Here's my database configuration:

 Log file size (4KB)                         (LOGFILSIZ) = 125000
 Number of primary log files                (LOGPRIMARY) = 10
 Number of secondary log files               (LOGSECOND) = 0
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = /db2/C21/log_dir/NODE0000/LOGSTREAM0000/
 Overflow log path                     (OVERFLOWLOGPATH) =
 Mirror log path                         (MIRRORLOGPATH) =
 First active log file                                   = S0025598.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = YES
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

 Percent log file reclaimed before soft chckpt (SOFTMAX) = 100

Now /db2/C21/log_dir utilization is 100%. What  to do in this situation?


texts: Create job to send alert using sp_send_dbmail when table value greater than x

texts:
PROBLEM

The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.

TABLE CREATE

CREATE TABLE [dbo].[SMSCounter](
  [Counter] [int] NOT NULL,
  [CounterDate] [datetime] NOT NULL )

EXAMPLE DATA

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

JOB SCRIPT

The script below is my attempt to create a scheduled job that will check every hour if the SMScounter.Counter is greater than 950.

USE DATABASEname
select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate  = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)
IF CHECKVALUE > 950

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =
    'USE DATABASEname
     (select * from SMSCounter where CounterDate = GETDATE())'

END

QUESTIONS

How can I format GETDATE to examine dd/mm/yy and ignore the time part when reading SMScounter.CounterDate? (script above edited , works now).
How can I get the IF statement to work to compare CHECKVALUE to 950?

I'm using SQL Server 2008 standard edition.


texts: Performance of different precedence pseudocolumn solutions with LIMIT

texts:
I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.

The OP wanted to get the first matching row given a set of conditions with decreasing precedence.  Both solutions involved a pseudocolumn, but one (mine) involved multiple SELECT statements UNION ALLed together, while the other one constructed a CASE expression.

I share my results in the hope that somebody will find this useful.


texts: SQL Server - login failure every day at same time with the service account

texts:
I have dozens of SQL Servers, on many different versions. Many of them have an error in the SQL Server error log EVERY DAY at just about 2 am.

Login failed for user 'TheDomain\SQLServerAgentServiceAccount'

Some show this reason:

Reason: Token-based server access validation failed with an infrastructure error.
Check for previous errors. [CLIENT: <local machine>]

Error: 18456, Severity: 14, State: 11.

The rest show this:

Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.

Our network team doesn't see anything suspicious in this time frame. The second reason makes sense and is true - there ISN'T a matching login for the service account. I don't know why there would be an explicit one created for a service account.

What could the servers be trying to do at the same time every day? There are no Agent jobs, backups, maintenance plans, or any other known services that would do anything.


texts: MongoDB - Stand Alone Server Security SSL or SSH

texts:
We've outgrown our current server and decided to move to two servers, one to host our apps, and one to hold our database. We looked into clustering and we don't feel we need to scale horizontally just yet.

What's the best way to connect to and secure communication between the database and app server. Mongo suggests SSL in their docs but Digital Ocean now allows private networking between servers so I'm thinking maybe setup an SSH connection between the two servers? I'm not sure how reliable that would be. Also, I don't want to pay for the data transfer if I don't have to, so I'd like to keep it on private networking. What's the best way to get this done.


texts: Creating crosstab() pivot table in PostgreSQL 9.0

texts:
I have a rather complicated issue for myself which I hope someone can help me with.

I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.

To keep it simple I will just show the table structure i want to use for the pivot and then use the methods given to create others, I hope :)

The fields I want to use are postcode_nospace_, and ageband.

ageband will be the columns across the top with postcode_nospace_ being the rows with a count of each postcode being the data.

There are 2132 records and of which some have more than one postcode, hence the count.

CREATE TABLE adult_social_care.activities_in_localities_asc
(
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

UPDATE:

Here is the outcome I want to achieve as shown in an excel pivot table with the same data.

postcode 18_24  25_34   35_44   45_54   55_64   65_74   Total Count
----------------------------------------------------------------------------
BB115DE     1           2                   2      3       8
FY38LZ                              1       1              2

From looking around I have compiled the following SQL query. It groups by postcode and creates the field names required. However the fields are blank. I would ideally also want a total_count column at the end of the table.

SELECT * FROM crosstab(
    'SELECT postcode_nospace_, ageband, count(ageband) as total_count
     FROM adult_social_care.activities_in_localities_asc
     GROUP BY postcode_nospace_, ageband
     ORDER BY postcode_nospace_'

     ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric);


texts: Why Does SSIS Oracle Source by Attunity return DT_WSTR for Oracle CHAR(1) Column?

texts:
Using SSIS 2012 and Microsoft SSIS Connector for Oracle by Attunity v. 2.0.

When I add an Oracle Source for a particular table, it is setting the data type for an Oracle CHAR(1) field to DT_WSTR.

I have read documentation and searched the web, and I would expect an Oracle CHAR(1) to be DT_STR and not DT_WSTR.

Does anyone know why this is happening?

Further, when I try to set the data type in my Oracle Source to DT_STR for the column, the setting doesn't seem to stick.

Anyone encounter this, and do you have any suggestions as to how to work around it?


texts:
I'm attempting the pivot below:

SELECT
   EmpID as ID,
   [1] as January,
   [2] as February,
   [3] as March,
   [4] as April,
   [5] as May,
   [6] as June,
   [7] as July,
   [8] as August,
   [9] as September,
   [10] as October,
   [11] as November,
   [12] as December,
   isnull((processeddate),' ') as 'Null'
FROM
(
  SELECT EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth
  FROM [dbo].[Invoices] where Inv_Number not in ('1035','1039')
)
AS source
PIVOT
(
  sum(EmpDeductAmt) for TMonth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)
AS pvtmonth
ORDER BY EmpID;

However, I receive an error message of "Invalid column name 'processeddate' upon hitting F5.

I'm fairly green, but the code worked beautifully when the isnull((processed date),' ') as 'Null' code was left off.

The desired output would consist of columns pertaining to each month in addition to a column for NULL months, as some of the records in my dataset have NULL as their date. Failure to account for the NULL dates shortchanges the dollar amount arrived out. The rows  are arranged by employee identification number.

Is there a suggested remedy for this issue?

Many, many thanks!


texts:
I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA to convert these tables in SQL Server.

It is part of a migration through SSIS. I have to merge all these tables in one table without duplicates. A portion of the treatment is the same for all the tables but there are some subtleties. One problem is that the column names have slight differences (like X_Y instead of X). And each table have specific columns but I do not want to retrieve the data in those.

I hope it is not too broad a question, but:

Is it better to try to do a generic part, taking the table name as a variable to apply the same treatment with conditions in it to take care of the differences?
Or should I just copy & paste a core/template data flow and manage each difference in separate packages?


texts: Users on same server connect to MySQL with different hosts

texts:
I am aware that when a connection is made to MySQL, MySQL does a reverse lookup on the IP address to determine the host it is coming from.

I have a very odd situation where I have a number of users connecting from the same server and some users are showing as coming from one host (lets say for example "myhost.com") and others are from a different host (lets say "myhost.co.uk")

The config of the connection in both cases is the same. The IP they connect from is the same. The server they connect to is the same. On both client and server PC the reverse lookup of the IP resolves to the same host (e.g. myhost.com). There is only one IP address on the server.

This is causing us problems with authentication because when adding new users (something which is an automated script) we add them using the server IP address as the host, and about 50% of the time, this is not working because when it does the reverse lookup it finds the wrong host name!

I am aware that you can turn off the host lookup in MySQL config, but at this stage I'm reluctant to do that and would rather find an answer to the problem.


texts: Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function

texts:
I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work:

SELECT    p_search.id, count(kia.image_id) AS count
FROM      (SELECT id,
                  keyword,
                  to_tsvector('english',keyword) AS tskw
          FROM keyword) AS p_search
LEFT JOIN keyword_image_assoc AS kia
ON        kia.keyword_id = p_search.id
WHERE     p_search.tskw @@ plainto_tsquery('english','resident life')
OR        p_search.tskw @@ plainto_tsquery('english','wofford')
GROUP BY  kia.image_id
ORDER BY  count desc

For context, I have three tables:

image which has an id as the primary key
keyword which has id as the primary key and keyword which is a string representing a keyword
keyword_image_assoc which has an id, image_id and keyword_id which is used to associate images and keywords canonically.

If I add p_search.id to the GROUP BY clause, the results aren't correct - count is always 1. Is there a way to group the results so the image_ids have the count of the number of matching keywords?

I'm looking for two columns count - the number of matching keywords and image_id the id of the corresponding image. Thanks!


texts: Postgres Logical Replication For Specific tables

texts:
I'm using logical replication to move data from postgres to a search engine via some background process, but I am only concerned with a small set of tables. Is there a way to specify which tables a replication slot is concerned with

I have a replication user set up and am able to receive the changes just fine.
I've implemented little script to handle the binary protocol and forward to my search engine with:

START_REPLICATION SLOT <SLOT NAME> LOGICAL 0/00000000

But this gives me every change for every table.
I see that this is possible between postgres servers via publications and subscriptions, but this doesn't seem to work from an application client. Maybe I am missing something?

Is there a way to whitelist tables for replication slots this way?


texts:
I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has been great - it replicates transactions from the Tandem-NSK source and can apply the changes into any supported database - they're using MySQL at the remote end. We're switching our billing system to Oracle, and while we could continue to use Goldengate to move these logs, I'd like to see if there's another option.

We initially chose Goldengate because nothing else could get data out of the Tandem NSK, but now that we're moving to Oracle, there may be some more native (or at least simpler) choices. I've got nothing against them - like I said, it works great - but I'm hoping that two mainstrem databases are easier to do replication between than the NSK.

Are there any products of methods that would help get transactional data from an Oracle system into an MySQL database? I'm not sure if there's any way to do this kind of replication natively (I know we can do Oracle -> MSSQL using native replication, but not any way to target MySQL that I'm aware of), or if anybody knows of a product that could facilitate this (and costs less than Goldengate).

Thanks for any suggestions!


texts:
I'm trying to run
impdp system/password dumpfile=FILE.dmp fromuser=USER1 touser=USER1 directory=$DIRECTORY on oracle server but keep getting following error:

LRM-00118: syntax error at '=' at the end of input

I've come to this after running the following commands on oracle server sqlplus:

alter session set container=sgeplus;
CREATE USER USER1 IDENTIFIED BY USER1;
GRANT ALL PRIVILEGES TO USER1 WITH ADMIN OPTION;
GRANT CONNECT TO USER1;
CREATE USER GEOC_WEB IDENTIFIED BY GEOC_WEB;
GRANT CONNECT TO GEOC_WEB;

And running this on oracle server:

expdp sgeplus/sgeplus@localhost/orcl full=y dumpfile=sge210b.dmp

I haven't found any similar error, so any help will be welcomed!

Thanks in advance.


texts: How to avoid Head Blocker query doing nothing?

texts:
We are experiencing periodic slowdowns of our SQL Server 2014.

Today I identified what appears to be the cause, in the SQL Activity Monitor.

Process 61 has:

Task State - blank
Command - blank
Wait time - 0
Blocked by - blank
Head block - 1    <-- ZOMG!!

Several other processes had:

Task State: SUSPENDED
Command: SELECT
Wait Time:  1000000+
Blocked By:  61     (or some blocking chain leading to 61)

The SQL for process 61 head blocker was a simple query (EntityFramework generated) doing what should be a fast indexed lookup on a single table:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ClientCareDocumentTemplateId] AS [ClientCareDocumentTemplateId],
....
[Extent1].[LastUpdatedByAuditLogId] AS [LastUpdatedByAuditLogId]
FROM [dbo].[ClientCareDocument] AS [Extent1]
WHERE [Extent1].[Id] = 185238

Why on earth would this query, allegedly not even doing anything, having no task state, having 0 wait time, be blocking other queries?

How can we avoid these occurring in future?


texts: Lookback Query utilizing Change Data Capture in SQL Server 2014?

texts:
I'm using SQL Server 2014 to maintain a small database and while looking for a way to keep a History of my Data I came upon the Change Data Capture Mechanism.

My question now is if there is an easy way to create a lookback query using Change Data Capture, so I could for example query against the database state from 3 Days Ago.

I could do all that with temporary tables but I kind of doubt I'm the first one thinking about this and maybe there's even a built in capability i missed.

Any other way to manage lookback queries is also appreciated :)


texts: SQL Server crash with Reason 0x00000006 after a while on linux

texts:
Environment

OS

Ubuntu 16.04 x86_64 (also happening on Windows)

DB

SQL Server 2017 with FullText Search module (mssql-server and mssql-server-fts packages)

Problem

The issue that I'm facing is that, after a while (I didn't find a pattern yet, but it happens from times to times... last time the database was in IDLE with just a DBeaver connection), the server crashes with a fatal error as follows:

mssql_1      | This program has encountered a fatal error and cannot continue running.
mssql_1      | The following diagnostic information is available:
mssql_1      |
mssql_1      |        Reason: 0x00000006
mssql_1      |        Status: 0xc0000005
mssql_1      |       Message: Last Chance
mssql_1      |       Address: 0x6a446912
mssql_1      |    Parameters: 0x802cef270
   Stacktrace: 000000006a4e84f6 000000006a43fb1e 000000006a43f9ca
mssql_1      |                000000006a518473 000000006a446912
      Process: 9 - sqlservr
mssql_1      |        Thread: 175 (application thread 0x1288)
mssql_1      |   Instance Id: 7eb1810a-0fbe-42f1-bb07-a0f20f7f3dcf
mssql_1      |      Crash Id: bec85ffc-9907-4f7b-a7bd-bd17e0c90561
mssql_1      |   Build stamp: 0fa9143612e88b9b2379174bbdc492bbfae2bec2f0278445d22e11dd124604ab
mssql_1      |
mssql_1      | Capturing core dump and information...
mssql_1      | No journal files were found.
mssql_1      | No journal files were found.
mssql_1      | Attempting to capture a dump with paldumper
mssql_1      | WARNING: Capture attempt failure detected
mssql_1      | Attempting to capture a filtered dump with paldumper
mssql_1      | WARNING: Attempt to capture dump failed.  Reference /var/opt/mssql/log/core.sqlservr.9.temp/log/paldumper-debug.log for details
mssql_1      | Attempting to capture a dump with gdb
mssql_1      | WARNING: Unable to capture crash dump with GDB. You may need to
mssql_1      | allow ptrace debugging, enable the CAP_SYS_PTRACE capability, or
mssql_1      | run as root.

this error reasons table (am I looking at the correct table?) points that:

Reason: 0x00000006 - The handle is invalid.

Message: Last Chance

I have no clue what is going on... so... Does anyone knows what is the real reason of the error here?

Any suggestions on how can I get more debug/runtime info in order to trace what is going on?

Thanks in advance!

[Update 1] It also happens on Windows:

This is also happening on SQL Server 2017 on Windows.


texts: Sync 8 tables of differents databases on same server

texts:
I will use eight wordpress websites and i need to sync ther wp-user tables so each single user can connect through all websites.
All of these will be on the same server, using the same MySQL user, but they will each have their own database.
The server will be a mutualised one, so i cannot install any other software that MySQL.

What i thought of doing was this :

create a master table which will collect recursively all other
tables.
When it's finished, it is supposed to have every information of the other up to date... Isn't it ?
Then i would sync all previous databases on this master one.

What do you think about it ?
But i don't know how to do that. I thought of using CRON to run that once a day, but i don't know anything about how to write MySQL scripts, and didn't understand much about the "MySQL Replication" topics i found until now...

Thank you in advance.

EDIT : Michael - sqlbot suggested using the "merge algorithm" to do what i need. I like what it looks like, but did not succeed using it. Maybe because I run MariaDB on Localhost. What do you thnik, and n=know about this solution ?


texts: How to extract the structure out of DB2 (Back up Structure)?

texts:
Is there a way in db2 to take a backup of the database structure (Metadata) without actual data?

My purpose is to pull structure (tables, views, indexes, triggers, procedures and functions) from an existing DB into a new DB.

The tables can be blank.


texts: Performance: Interleaving several statement handles in MySQL

texts:
Does interleaving several statement handles slow the system down?

Example (Perl):

$sth1->execute(...); $sth2->execute(...); $sth1->execute(...); $sth2->execute(...); (interleaved)

vs

$sth1->execute(...); $sth1->execute(...); $sth2->execute(...); $sth2->execute(...); (not interleaved)

(A real example would involve several hundred calls in a loop.)


texts: Do I understand this relationship correctly?

texts: So I made this bit of a diagram and I am not sure if I am going the right way in terms of understanding relationships. A character has attributes (Health, Mana, Power etc.) There are a quite a lot of attributes so I created a table that stores attributes and added a relation of an attribute with a character that has a value, all cool and all. Now for the IsValueType relation it gets more confusing. An attribute has a value type (Percentage, flat value etc.) so I created a IsValueType Relation that relates the attributeID with ValueTypeID, is this a one to many relationship like I drew it or is it a one to one relation ship as one attribute can only have 1 value type and 1 value type can only be linked to the same attribute (An attribute can not have 2 or more value types).


texts: DBATools - pass through flat file configs to Agent Job Commands

texts:
I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite.

Given $sa = Get-Credential and foo.config of the below form...

{
    "Schedule":                  "Foo",
    "Disabled":                  false,
    "FrequencyType":             "Weekly",
    "FrequencyInterval":         "EveryDay",
    "FrequencySubdayType":       "Time",
    "FrequencySubdayInterval":   0,
    "FrequencyRelativeInterval": "Unused",
    "FrequencyRecurrenceFactor": 1,
    "StartDate":                 "20180823",
    "EndDate":                   "20181023",
    "StartTime":                 "070000",
    "EndTime":                   "235959"
}

Attempting to use foo.config in a solution fails basic parsing with the following message:

~> $foo = Get-Content foo.config | ConvertFrom-Json
~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

  WARNING: [15:50:04][New-DbaAgentSchedule] A schedule was not provided! Please provide a schedule name.

Well that sucks... Especially since the following works just fine...

$bar = @{
    Schedule=                  "Foo"
    Disabled=                  $false
    FrequencyType=             "Weekly"
    FrequencyInterval=         "EveryDay"
    FrequencySubdayType=       "Time"
    FrequencySubdayInterval=   0
    FrequencyRelativeInterval= "Unused"
    FrequencyRecurrenceFactor= 1
    StartDate=                 "20180823"
    EndDate=                   "20181023"
    StartTime=                 "070000"
    EndTime=                   "235959"
}

New-DbaAgentSchedule @bar -ServerInstance "." -SqlCredential $sa

I really don't want to bother typing out each individual ... -Param1 $foo.Param1 -Param2 $foo.Param2 ... because I'm lazy. I would really much rather splat my config files into the various commands. Why won't this work!!?!1!


texts:
I have dropped one huge  table from the production DB

Current DB size on disk = 264 GB

The  real  size of the Tables = 32.164 GB

I am going to shrink this DB. Although it is not recommended, but this is the only option i have to reclaim this space, i will do monthly archiving for this table so i am sure it will not grow to this size again

My Questions are

There are Mirroring and replication on this DB, What should i do?

Is there a way to do this with no downtime? if no, what should be the plan to do this with the minimal downtime

Is Shrinking just affect indexes or it has other impact on performance?


texts:
When I run the SQL Server 2008 Setup Discovery Report, all of the components (Database Engine Services, Management Tools - Complete, etc) show as version 10.3.5852.0.

However, I cannot find any information online as to what this version relates to ?

Any advice or help gratefully received.


texts: Is there a "standard way" to log network messages (events) into a database?

texts:
I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event.
Messages are heterogeneous, meaning that the first "field" (always an integer) is the message type, the second is a timestamp (always an integer). The other fields differ depending on the message type.

I want to log those messages into a database. Messages are totally unrelated and there is no relationship between them (since they are related to different events).
My logger application receives packets and assign them an unique incremental integer.

Basically I'd like to know if there is a standard way to store those packets/events in a database.
The most useful SQL query I'll need is to get all events in the same order I stored them.

So far I come across two possible implementations.

Implementation 1:

For each event type I can create a different table, plus a "main table":

Table Main:  Counter (INTEGER Primary Key), Timestamp (INTEGER), MessageType(INTEGER) // MessageType is optional but useful
Table Msg1:  Counter (INTEGER FOREIGN KEY), Name(STRING), Surname(STRING) ...
Table Msg2:  Counter (INTEGER FOREIGN KEY), FieldFoo(INTEGER), FieldBar(INTEGER)...
...

If I need to get all the events I have to make a JOIN using the tables. Giving the fact I might have 15-20 events I could have a very huge JOIN.

Implementation 2:

Given that 90% of fields are INTEGER, I could create a maxitable:

Table Huge: Counter(INTEGER Primary Key), Timestamp(INTEGER), MessageType(INTEGER), Field1(INTEGER) ... Field5(INTEGER), Field6(STRING), Field7(STRING)...

Basically I'm not giving a role to fields here, and their meaning depends on the field MessageType. Not a very elegant solution but prevents me to make a JOIN using 15-20 tables.
I can still filter events of the same type thanks to the MessageType field.

Efficiency is not a problem in the inserting phase here because event packets are quite rare (and I'm aware of the fact that solution #1 requires two inserts for each event).

From the point of view of my application there isn't a very big difference: both solutions are just fine for my needs since I can get the very same information from both solutions.
Solution 1 seems more elegant (at least it's "normalized") but the idea of making a JOIN using 15-20 tables makes sound a ringbell in my head. I use SQLite by the way, does it have a limit on how
many tables I can join on the same SELECT query?

Is there a standard way to approach this kind of problem (i.e. logging heterogeneous events)? My guess is that this is a common problem and thus there is a standard solution.


texts: How to create database level counter (identify field)?

texts:
I recall that SQL Server (maybe version 2014 but it could be 2016) added the ability to create database level counters (identity field?).  Sort of like an IDENTITY function at the database level.  I don't recall what they called these counters so am having trouble finding them in the help.

What are these counters called and how do I create one?

Note that I am not talking about creating a table with an identity column and rolling a scalar valued function to return it.  I am referring to a build-in feature but I can't recall what it is named.


texts: How can I obtain concurrent deletes for this table?

texts:
We have a program that is currently doing something to the effect of:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

UPDATE [Source].[dbo].[SourceTable]
SET [ProcessedOn] = GETDATE()
OUTPUT DELETED.* -- Output is captured via bcp
WHERE [Source] = 0
AND [Destination] = @Destination

-- Bulk copy from Source to SourceHistory via SqlBulkCopy using:
-- SELECT * FROM [Source].[dbo].[SourceTable]
-- WHERE [Source] = 0
-- AND [Destination] = @Destination

DELETE FROM [Source].[dbo].[SourceTable]
WHERE [Source] = 0
AND [Destination] = @Destination

COMMIT

We have multiple clients that execute this code against a central server. Each is executing with a unique @Destination variable.

The SourceTable has the following schema:

CREATE TABLE [dbo].[SourceTable]
(
    [Source]        int       NOT NULL,
    [Destination]   int       NOT NULL,
    [SourceTableId] bigint    NOT NULL IDENTITY(1, 1),
    [Data]          int       NOT NULL,
    -- Other related "data" fields
    [ProcessedOn]   datetime2     NULL,

    CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED ([Source], [Destination], [SourceTableId])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Our issue is that we are, as we might expect, running into process blocking between clients that are executing these queries.

Is there anything we can do to minimize blocking between distinct clients? Each client will always execute with a unique @Destination. We have the transaction in place not to prevent two clients from updating the same data, but to allow rolling back an individual client if an unexpected error occurs.


texts: What does a "Buffer cache hit ratio" of 9990 mean?

texts:
I got this query from a blog post:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

The post said that it would give me a percentage of hits to the cache.  It seemed to indicate that it would be a value of 0-100 (it showed a result of 87).

But when I run it I am getting very high numbers.  Here is an example:

object_name               counter_name             cntr_value
SQLServer:Buffer Manager  Buffer cache hit ratio   9990

Does this mean 99.90 %?

If not, what does it mean?  And how can I get the real value?

NOTE: I have gotten values as low as 257 and as high as 352363

Incase it is relevant, here are a few other server stats:

Page life expectancy: 145
Page reads/sec: 1,380,009,009


texts: Is it recommended to have the MySQL database location as the default one which is /varlib/mysql ? Are there any risks in having the databases in the specified location?


texts:
I would like to ask a couple of things about MongoDB and replica sets:

I have different meteor applications running on my private server; each one of them uses a different meteor database (and does not need to access other databases); right now only one mongod instance is running on the server, serving all applications; should I run different mongod instances (one for every application)?

I would like to create a replica-set with oplog; my question is: this is only useful for performance when there are a lot of requests (coming from many different clients) or will improve performance also in every single request? (The different members of the replica set will cooperate to find faster the documents requested by a single client?)


texts: Will SQL 2008 SSIS package files work with SQL 2016 databases after database upgrades?

texts:
We plan to upgrade SQL 2008R2 to SQL 2016. We want to upgrade database servers first. We have separate SQL 2008R2 servers for SSIS and SSRS. SSIS packages are stored as files.

My question is:

After upgrading all database servers to SQL 2016, will all the SQL 2008R2 SSIS and SSRS servers work with the SQL 2016 databases?

Appreciate your experience.


texts:
This question is related to my old question. The below query was taking 10 to 15 seconds to execute:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0)

In some articles I saw that using CAST and CHARINDEX will not benefit from indexing. There are also some articles that say using LIKE '%abc%' will not benefit from indexing while LIKE 'abc%' will:

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where
https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries
http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

In my case I can rewrite the query as:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

This query gives the same output as the previous one. I have created a nonclustered index for column Phone no. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.

How does LIKE '%123456789%' benefit from indexing?

Why do the listed articles state that it will not improve performance?

I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

Query using CHARINDEX:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 )

Execution plan:

Query using LIKE:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

Execution plan:


texts:
Run command for pt-kill

/usr/bin/pt-kill --ignore-state User lock --rds --match-command Query --victims all --match-user phppoint --daemonize --busy-time 10 --kill --print --log /var/log/pt-kill.log h=HOST,u=master,p=PaSS,P=3306

Queries are killed but not logged. Runs as root on Amazon Linux and file /var/log/put-kill.log exists and has 600 permissions


texts: SQL Server partitioning of many-to-many tables - triggers

texts:
Many to many tables usually have only foreign keys to related tables, like

EmployeeId
PositionId

Is it good idea to add column only for partitioning and set its value with INSTEAD OF INSERT/UPDATE trigger from one of the related tables that has such a column?


texts: Does the recovery model affect growth of the .mdf file?

texts:
On my production database, the .mdf file is rapidly growing on a daily basis.

If I change the recovery model from SIMPLE to FULL, will that help?


texts: Using if statement outside of a stored procedure or function

texts:
I want to call a query to update a mysql table but my query contains an if statement

set @id = 0;
set @the_number = 0;
set @the_message = 0;
set @selected_message = 0;

SELECT id, dest_msisdn, text_message INTO @id, @the_number, @the_message FROM    incoming_sms where service_id = 6015592000101762 AND job_status = 0 limit 1;

if(@the_message LIKE '%Bank%')then

select 'h';

/* Update Statement Here*/
else

select 'nothing to see here';

end if;

This is the error that i get.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(@the_message LIKE '%Bank%')then

select 'h'' at line 1

In the mysql manual found here for if http://dev.mysql.com/doc/refman/5.0/en/if.html
it explains The IF statement for stored programs implements a basic conditional construct....

Are If statements usable outside stored procedures?.


texts: Dedicated Resources on VMs hosting SQL Server

texts:
My understanding is that one of the big benefits of using a VM is that you can share resources between the virtual machines on a host.  So you can have a host with 120 CPUs and put 5 VMs on it with 32 CPUs each.  The extra CPUs are "shared" between the VMs with the host assigning CPUs dynamically based on need.  Same for memory.

I also understood that this is a big no-no for VMs that house SQL Servers but my VM admins disagree.  Does anyone have any evidence or documentation one way or the other?

I don't know if it matters but we are using VMWare.


texts: MySQL cannot create foreign key, errors 1005, 150

texts:
I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:

ALTER TABLE `MyDatabase`.`MyTable`
  ADD CONSTRAINT `myNewForeignKey`
  FOREIGN KEY (`TheId` )
  REFERENCES `MyDatabase`.`MyReferencedTable` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `myNewForeignKey_idx` (`TheId` ASC) ;

When applied I get:

ERROR 1005: Can't create table 'MyDatabase.#sql-1ab6_5c3df97' (errno: 150)
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'MyReferencedTable' already exists

I even tried removing the table (could do that as it's empty) and recreating it with foreign keys and all, but it leads to the same errors.

I've seen reports of bugs relating to this, but they are several years old and should've been fixed by now. Surely foreign keys must be possible to add. What am I doing wrong?


texts: Where does the mysqldump command store the backed up databases?

texts:
I am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful. Where does it place the backed up databases?

I run the command like this:

$ mysqldump -h localhost -u username - p database_name > back_up_db.sql

but have no idea where back_up_db.sql is stored.


texts: Is there any benefit to having both an ordered and unordered index on the same column in postgres?

texts:
I have inherited a postgres database where there are two identical indexes except for their order:

CREATE INDEX avail_time_ix ON table (availability_time);
CREATE INDEX avail_time_dc_ix ON table (availability_time DESC);

Is there any benefit to having both the ordered index and the unordered index?


texts: Merging two databases and sequence on Primary Key column

texts:
I'm using Database Migration Services on AWS to migrate data from 2 DBs and merge into 1.
The problem is with existing sequence on id column(in both databases) which is Primary Key too. I want to find the best way to merge this data without causing duplicate value error.
Note : DB structure for both are same.


texts: Difference in between MySQL affected num rows and num rows

texts: I am just newbie to the MySQL.I want to know what is the main differences in between mysql num rows and mysql affected num rows?Any example or references will be highly appreciable.


texts: MariaDB - MySQL - ALL-DB Import - Table 'user' already exists

texts:
I am attempting to migrate from local MariaDB to Docker version which should in essence be as simple as migrating to a new SQL Server. I have setup the Docker container fine via but can't seem to import my "all-databases" dump.

This is what I get:

mysql -u root -p  < mariadb_alldb_*.sql
Enter password:
ERROR 1050 (42S01) at line 8022: Table 'user' already exists

Dump generated via :

mysqldump -u root -p --all-databases --skip-lock-tables > mariadb_alldb_"$(date '+%F')".sql

Update: This is run on a fresh docker container each time and I have created backups in a directory that I am importing from.

ls * | grep mariadb_alldb_
mariadb_alldb_2020-05-04.sql

Update2: Perhaps it's related to my docker setup?

Here is my docker cmd:

docker stop mariadb && docker rm mariadb
docker run -d --name="mariadb" \
-p 3306:3306 \
-e TZ="America/Whitehorse" \
-v "/opt/mariadb/conf/conf.d":"/etc/mysql/conf.d" \
-v "/opt/mariadb/backups":"/mnt/" \
--mount type=volume,dst=/var/run/mysqld,volume-driver=local,volume-opt=type=none,volume-opt=o=bind,volume-opt=device=/var/run/mysqld \
mariadb:latest

I am doing this import from the local machine, I have reproduced results from inside the container.


texts:
After patching GI Home and DB Home simultaneously using command:
.oraenv
+ASM1
opatchauto apply /patchlocation

Should i run ./datapatch from DB home or GI home? Or it doesn't matter from which home? Thanks


texts:
Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:

INSERT INTO bins (id)
VALUES (1)

to create a single row with id '1'

How do I create 10000 rows with corresponding id number?

Version: PostgreSQL 9.5.5


texts: How to fix errors reported by DBCC CHECKDB

texts:
I am getting a lot of errors from DBCC CHECKDB, including:

  Msg 8939, Level 16, State 98, Line 1
  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 3667181342891245568 (type Unknown), page (7791:-1694668604). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -12.

  Msg 8928, Level 16, State 1, Line 1
  Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data): Page (1:194923) could not be processed.  See other errors for details.

  Msg 8976, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194923) was not seen in the scan although its parent (1:186194) and previous (1:194922) refer to it. Check any previous errors.

  Msg 8980, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Index node page (1:186194), slot 103 refers to child page (1:194924) and previous child (1:194923), but they were not encountered.

  Msg 8978, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194932) is missing a reference from previous page (1:194931). Possible chain linkage problem.

Most importantly, at the end, the following is relayed:

  CHECKDB found 0 allocation errors and 28 consistency errors in database 'houseme'.

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (houseme).

What do I do?


texts: percona-online-schema-change is taking a lot of time

texts:
We have a table with 600M+ records and using AWS Aurora with master-slave replication.

Requirement:
We need to add two columns with index in customer table.

Aurora Instance Specs:
Ram: 30.5 GB
CPU: 4 vCPU

I replicated the cluster and created instance with same configuration and ran the update process to newly created master instance to see how it works. But it's taking a lot time; ( already been 4 days ) and still running. Is there anything I can do to speedup the alter process?

Command:

pt-online-schema-change --execute --ask-pass --user=admin --statistics --chunk-time=1 --alter 'ADD COLUMN email_sha1 CHAR(40) DEFAULT null, ADD COLUMN email_sha256 CHAR(64) DEFAULT null, ADD INDEX email_sha1(email_sha1) USING BTREE, ADD INDEX email_sha256(email_sha256) USING BTREE'  D=example,t=customer,h=example.public.rds.amazonaws.com


texts: MongoDB how to achieve asynchronous geo-replication

texts:
I have read a lot of background about MongoDB replication and sharding. None of the default solutions that I know can actually do what I'm trying to achieve.

Current situation

We have a time critical application balanced over three geographically seperated locations (called A, B and C) that generates reports as JSON documents and writes them on a centralized (external) MongoDB server. We have a different application that reads and processes these reports from the centralized MongoDB server. This works pretty well.

  Application (A) ---->  +---------------------+
                         |                     |
  Application (B) ---->  | Centralized MongoDB | --> Processing
                         |                     |
  Application (C) ---->  +---------------------+

The problem with our current setup is that if the centralized MongoDB server is unresponsive for some reason, the reports are lost and the application gets a nice performance penalty when it's waiting for a response. So basically three independant redundant locations are all relying on a single location for report storage. We are not able to fix this problem in the application.

Desired situation

We would like to have a local MongoDB service listening in each geographically seperated location, so that our application is always able to delegate the reports locally. But eventually we still want to have all reports from these three locations merged on the centralized MongoDB server.

                       +---------------+       +-------------+
  Application (A) -->  | Local MongoDB | ----> |             |
                       +---------------+       |             |
                       +---------------+       | Centralized |
  Application (B) -->  | Local MongoDB | ----> |             | --> Processing
                       +---------------+       |   MongoDB   |
                       +---------------+       |             |
  Application (C) -->  | Local MongoDB | ----> |             |
                       +---------------+       +-------------+

So the way I see it, this is kind of reversed sharding.

Some side notes:

This is a one way street. Our application does only inserts. It does not need to read data from storage. On the processing side is only reading required. It does not insert any data.
It would be acceptable if some reports are missing during an outage
It would be acceptable if the reports are out of order in the centralized storage
The replication should be asynchronous, but near real-time. We need to process the reports in a matter of seconds
If the local MongoDB process is also using local storage, that would be fine but we don't require it.
Any open source or commercial solution would be acceptable, also third-party solutions.
It's critical that the local MongoDB process always accepts the report immediately, so the application can do something else right away
The application requires MongoDB, so we need to stick with this.

Any help or thoughts would be highly appreciated.


texts: Terminology for one-to-one, many-to-one relationships concepts?

texts:
Some data is structured so there is only one piece of information that can be associated with an entity.  For example, a table with columns (id, name, phone) implies that each id has one name and one phone number.

Some data is structured such that there can be multiple repeated pieces of information associated with an entity.  For example, multiple keywords can be associated with an entity by repeating rows in a table (id, keyword).  We commonly refer to this as a 1-many relationship.

Are there "non-database" words to describe these concepts?


texts: Data Flow Task Produces No Rows with Declare Statement

texts:
I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I have also simplified the package to just do a row count for the purposes of this demo.

This issue I've having is that the query produces rows in SQL server but produces no rows in SSIS. The query is as follows.

DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)

Please ignore the * as that is just for the purposes of this demo. The actual query has the same issue even though it does not have the *. When Executed in SQL server this returns 1 row from the calendar table as expected. However when executed against the same environment in SSIS I get no rows returned.

If I turn the query into a stored procedure and execute the sproc in SSIS I get a row count of 1.

If I alter the query to this.

SELECT
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' )) AS DATE)

Then I once again receive 1 row.

Is there any reason I'm seeing this behavior?

UPDATE:
Before I receive a ton of flack for this I have no choice on the NOLOCK nonsense.


texts:
I installed Oracle 11g on my computer.
When I export data from Oracle 10g, I get the following error code. I have translated the error in parenthesis.

 EXP-00008: 遇到 ORACLE 错误 904  （encouter oracle error 904）
 ORA-00904: "POLTYP": 标识符无效   (POLTYP: is not valid )
 EXP-00000: 导出终止失败           (export fail)

Does someone know why? I believe it is ok to export from Oracle 10g using Oracle 11g client.


texts: Single Slave with Multiple Masters. Does this pattern exist in MongoDB?

texts:
Can we have a single mongoDB remotely (online) which can act as a slave to multiple master mongoDBs (which operate offline)?
For example, I have huge number of transactions which I like to perform on my local MongoDBs parallely on multiple systems (offline).
At the end of the day, I want to push minute part of my offline MongoDB records to my single slave MongoDB which is remote and online.
Does this pattern exist?


texts: Postgresql, escape character in string_agg function

texts:
Question, can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is.

{name:"připojení",value:"1/2""},

I need this (use \ before " only for fields/vars inside "param_name" and "value", in case, when value has " character.

{name:"připojení",value:"1/2\""},

Thanks.

Update, no json, I need export to csv. Some code here.

copy(

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",

...

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where

...

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids

) TO stdout DELIMITER ',' CSV;

It returns this.

... {name:""připojení"",value:""1/2""""},{name:""připojovací rozteč"",value:""558 mm""},{name:""rozměry"",value:""600 x 960 mm""}",""

But I need this.

... {name:"připojení",value:"1/2\""},{name:"připojovací rozteč",value:"558 mm"},{name:"rozměry",value:"600 x 960 mm"}","


texts: Is there a way to provide a client without administrator access additional information when they receive Error 18456?

texts:
When a user fails to log in to SQL Server, they receive this error:

  Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456)

On the client side, this error is always State 1. This is deliberate; per the documentation:

  To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

This makes sense, of course. However, in a non-production environment that is scrubbed of any sensitive data, it would be nice to allow users to see why their connection is failing instead of needing an administrator to check the logs. Is there any way to override this behavior and allow a user to get the true error state?


texts: Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required

texts:
We have a fairly high end box running mysqld 5.5.20.

The cpu configuration is dual quad core xeons.

It has 32 gb ram.

It has a quad intel ssd configuration running raid 0+1.

On a fairly busy service what starts to happen is that after about 18 to 24 hours of being up, once in a while, every few hours, there will be a 1min period where ALL inserts will start stacking. The strange thing is that using mytop even memory table inserts / updates are shown as executing for 'many' seconds.

More ofen then not even without a restart it will return to normal.... only to do it again.

A restart always fixed the issue, for another 18 to 24 hours.

All munins look ok.

Help!


texts: What does Then 1 else 0 mean in a CASE expression used with an aggregate function?

texts:
SUM(CASE WHEN column1 = 'value1' THEN 1 ELSE 0 END),
SUM(CASE WHEN column2 = 'value2' THEN 1 ELSE 0 END)..

I don't know why I am finding it difficult to understand what exactly is happening in the above statement and what exactly is 'Then 1 else 0' doing here.


texts: How to Repair "Orphaned" Database in SQL Server 2016

texts:
So, in my haste, I ran a script to create a database but I did not select the
full script before running it. I ended up with a bunch of tables without a "parent" database. Then I created a database intending to somehow attach the tables to it. But I have no clue on how to do it.

Short of dropping all the objects one by one and rerunning the original script, or changing the name of the database and running the script, is there a way of attaching the tables to the database? Tables are empty, i.e., no rows values have been inserted into them yet. Maybe using ALTER statements to attach tables to database?


texts:
I am wondering if it is possible to write SQL statements that are 100% interoperable with most or all databases including:

MariaDB/MySQL/Percona
Postgres
Microsoft SQL
Oracle
SQLite

(Eg, can I just follow a specific SQL standard? Eg, is there something similar to POSIX compliance standards for SQL?)

If so, are there any linting tools available that I can use in a git post-receive hook to reject SQL usage that doesn't follow such a standard or non-compliant SQL code without having to try to commit the code on all DBs?


texts: Can an Oracle IDENTITY column be nullable?

texts:
I want to create an ID column that automatically generates unique ID numbers by default.

Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box application (using the attribute editing environment).

I've heard that auto-generated IDs can be achieved by creating an Oracle IDENTITY column
 (hint: to find the relevant section in the linked page, search for this text: Use this clause to specify an identity column.).

And so, I have successfully created a table with an IDENTITY column:

CREATE TABLE A_TEST_TABLE (ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

However, I see that the field is not nullabe:

SELECT ID FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'A_TEST_TABLE'

NULLABLE = N

This is a problem because my application tries to "do users a favor". If a field is not nullable, the application automatically inserts a zero as a dummy value. This overwrites the auto-generated ID (which of course, is not what I want). I'm guessing that it does this to avoid getting an error message from the database if a null value is committed.

To avoid this issue with my application, is it possible to make the field nullabe?

When I read the documentation, it says:

  If you specify ON NULL, then Oracle Database uses the sequence
  generator to assign a value to the column when a subsequent INSERT
  statement attempts to assign a value that evaluates to NULL.

Does this imply that an IDENTITY field can be nullable? If so, how?

I recognize that I have no idea what I'm doing. I don't even think I would ultimately want an ID field that is nullable. I'm just trying to make sense of all this, and I'm failing.


texts:
There are a lot of blog postings about splitting tempdb into multiple files, and I understand the logic and reasoning behind this, but there is one question that never seems to get asked about a multi-tempdb architecture. TempDB file growth and disk-space consumption.

The argument is always to grow tempdb files at the same rate to prevent undoing the benefits (and I may be about to answer my own question here).

Does this mean that a query when processed cannot make use of all the tempdb files? If I had a single tempdb of 100MB and most queries needed 75% of this file to process.

Then I decided to split the tempdb into 4 logical files of 25MB each (giving me the same 100MB in total). If a query cannot work with mutliple files at the same time, then it won't take long before all the tempdb files are grown to 75-100MB each and I've effectively quadrupled the space requirements.

And the more files you add to tempdb the more overall disk-space you have to commit. IS that correct or does the query processor use any combination of files if needed?


texts: Backup and restore SQL server database with FILESTREAM filegroup

texts:
I use SQL Server and have a huge database with two filegroups:

Primary: Which contains all the data except the large files (1MB+)
FILESTREAM (read/write): Which contains the large files

Now, the backup scenario is:

Each Friday get a full backup (2 A.M)
Each day on week except Friday get a differential backup (2 A.M)

Since the database is large, and it is in production on a remote server, whenever I want to bring the database to my local environment to create a test database (weekly), I have to bring both the primary and the filestream.

I would like to be able to change the way the backups and restores are done, in such a way that only had to bring the primary filegroup, ignoring the filestream. By this way, every week I would only bring the primary filegroup, and not all information that suppose the filestream.

I think there can be a lot of problems, and all filestream references can be lost when accessing the files. I would like to know if is it possible to modify the content of all filestream columns when performing a backup, or use a different filestream hosted in the test environment. Also, I've heard about Piecemeal Restore of only some Filegroups, but I have many doubts on how to carry it out.

Question 1: can I have this scenario?

Question 2: is it a good idea to have only one Full backup and bring differential backups/transaction logs to test environment?

Question 3: can I have better scenario to backup and restore?

I'm all ears to recommendations. If you have any example case, please show me with a T-SQL query.


texts:
I am trying to back up an oracle database from OEM, but upon completion the job report says the job failed with the following error:

RMAN-03002: failure of backup command at 07/09/2013 10:26:52
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file C:\APP\RM\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file

A DBA set this database up but he is not currently available. I have tried running a crosscheck and a delete expired in RMAN but experienced the same problem when trying again.

The path it is looking in: C:\APP\RM\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC

...does not exist on the server, but it did exist on an original machine from which the DBA copied the database. The path containing the archive log now is:

C:\APP\CS\FLASH_RECOVERY_AREA\RONNIE\ARCHIVELOG\2013_06_10\O1_MF_1_1508_8VDHNOLY_.ARC

Can anyone help?

Thanks


texts: How does "OtherServer" in SSDT database references work?

texts:
I want to create a SSDT database project that is supposed to be deployed on a Sql Server that has several linked servers to other servers. The database ("MyBusinessDB") on those remote servers is in another SSDT solution, so I have this as "MyBusinessDB.dacpac".

Since we are allowed to add references to other databases or DACPAC files to correctly reference database objects, I would like to add the MyBusinessDB.dacpac as a reference to my project.

This article by Peter Schott explains the basics about adding database references.

There it's also possible to define that the referenced database resides on "Other/Different Server".

I referenced the MyBusinessDB.dacpac file and configured it to be on "same server". It's possible to use it's schema objects and reference them in Sql code.

But since I want to use this database on remote servers, I need to add a linked server name in advance. The Linked Server name should be variable because there are several instances. Unfortunately as soon as I configure the database reference to be on another/different server, visual studio fails to validate the schema objects of the referenced MyBusinessDB.dacpac file.

I tried different syntax, also the suggested syntax by the reference creation dialog itself (sample/suggestion is visible on bottom of this window):

SELECT * FROM [$(OtherServer)].[$(MyBusinessDB)].[Schema1].[Table1]

My naive expectation is that it's understood as: "I know what you mean, you are referencing the MyBusinessDB.dacpac by the variable "MyBusinessDB" and telling me that you want to use this on "OtherServer" you are going to fill with a value lateron..."

But using this I also get compiler errors. The dacpac's schema is never resolved.

Since I did just reference the exact same dacpac file and just added another variable (servername) in front of it, why is this failing to correctly read the dacpac's schema? How should this "feature" be used correctly?


texts: Getting error "Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7." But a database with Id 7 DOES NOT exist

texts:
I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to:
1) copy database back up from the prod.
2) DROP all existing databases except dba_utility
3) restore the backup
4) run dbcc checkdb

I frequently get:

Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.

The weird thing is, there is no database with an Id of 7.
Maximum Id in sys.databases catalog view is 6. (Remember, the job DROP all databases before the restore operation except dba_utility whose Id=5. The newly restored database is assigned the Id of 6.)

Prod environment is 2008 R2, test environment is 2012 SP1.

What might be causing this?


texts: Replication with two side update but one side insert

texts: I have server A and server B. I want server A can see data and changes on specific article on server B . But server B just see its own data . And also I want to update server B's data on both servers and data remain consistent. I want to prohibit the server B to see server A data As simple word I want replication with one way insert on subscriber and two way update on publisher data on both servers. Is it possible ? If it is how? Thanks


texts: Cannot find the Order Entry folder in $ORACLE_HOME/demo/schema/

texts:
I have downloaded and installed the most recent version of Oracle 12c (12.2.0.1.0). As stated on the page  https://docs.oracle.com/database/121/COMSC/installation.htm#COMSC00005,   I expected to find  oe_main.sql  in the folder $ORACLE_HOME/demo/schema/order_entry.  There is no such folder as you can see in my screenshot. I've only got the human_resources one. Why? Where are the others? Could anyone send them to me via e-mail? I am mostly interested in OE and OC . I have found some scripts on github, but I had serious problems due to many errors.

All the best!

UPDATE

Trying to execute the code  perl -p -i.bak -e 's#SUB__CWD#'$(pwd)'#g' *.sql /.sql /.dat  ,  I get the following errors:

Scalar found where operator expected at -e line 1, near "'s#__SUB__CWD__#'$(" (Missing operator before $(?)

Bareword found where operator expected at -e line 1, near "$(pwd"Missing operator before pwd?)

String found where operator expected at -e line 1, near ")'#g'" (Missing operator before '#g'? )

syntax error at -e line 1, near "'s#__SUB__CWD__#'$(" Execution of -e aborted due to compillation errors


texts:
I have a table with no primary key defined, partitioned on a column, using List Partitioning method. It has huge data in one partition. Though partitioned column is used in WHERE clause while querying against this table, performance is very slow.

Is it suggestible to create an index that includes the partitioned column? Here's my sample table structure,

CREATE TABLE inventory
(
   AccountId BIGINT,
   AccountNumber VARCHAR(250),
   DeviceId BIGINT,
   DeviceNumber VARCHAR(250),
   ClientId SMALLINT
   CreatedDate DATETIME,
   CreatedUser VARCHAR(50)
)
PARTITION BY LIST (ClientId)
 (PARTITION p_1 VALUES IN (1),
  PARTITION p_2 VALUES IN (2),
  PARTITION p_3 VALUES IN (3),
  PARTITION p_4 VALUES IN (4),
  PARTITION p_5 VALUES IN (5),
  PARTITION p_6 VALUES IN (6),
  PARTITION p_7 VALUES IN (7),
  PARTITION p_8 VALUES IN (8),
  PARTITION p_9 VALUES IN (9),
  PARTITION p_10 VALUES IN (10),
  PARTITION p_11 VALUES IN (11),
  PARTITION p_12 VALUES IN (12),
  PARTITION p_13 VALUES IN (13),
  PARTITION p_14 VALUES IN (14),
  PARTITION p_15 VALUES IN (15));

Table is partitioned by "ClientId" and I had to make 1024 partitions to this table.


texts:
Is there any convention for database names in MySQL/MariaDB? I've searched Google and this Stack Exchange and haven't been able to find anything. Not even a "Nope, there's no official convention." Is there a standard, or is this something that's decided on a case-by-case basis?

Thank you.


texts:
I have SQL Server 2016 RTM installed and would like to update it to SP1. When I run an SP1 cumulative update, it fails because there is nothing to update. Presumably this is because it needs SP1 to be installed first. When I run the SP1 installer, it wants to create a new instance.

How can I update an existing instance from RTM to SP1 without having to uninstall and reinstall the instance?


texts: How to set max user connection per database in mysql?

texts: Hi i am new to MySQL and currently using version 5.5, currently i have one instance for MySQL, with 50 databases hosted on that instance. We have identified that per database there were fix amount of users connection per database is getting used like in my case its 100 users are active on that database. we would like to know is there any specific setting under which this behavior is getting observed?


texts:
Is it possible to monitor disk IO usage on an Azure VM or in SQL Management to detect whether disks are being throttled? How would you do it?

Background:

I'm looking for a way to raise an alert when disk IO throttling begins on an old legacy system. The legacy system has a dedicated SQL Server 2016 instance running multiple databases per client. It runs as a virtual machine on Azure.


texts: MongoDB admin user cannot access system.views collection

texts:
One of our MongoDB databases contains a collection called system.views and I cannot find any information for this collection in Mongo Doc:

https://docs.mongodb.com/manual/reference/system-collections/

When I use admin user to execute command:

db.system.views.findOne()

It returns an authorization error:

"errmsg" : "not authorized on <database> to execute command { find: \"system.views\", filter: {}, limit: 1.0, singleBatch: true, lsid: { id: UUID(\"98bfa592-4d51-4d4a-964c-687c97444a2c\")}, $clusterTime: { clusterTime: Timestamp(1568022175, 1), signature: { hash: BinData(0, 316762B835CC7EBA0A70B314421B9366D679415C), keyId: 6723325935254241281 } }, $db: \"<database>\" }",

Admin user has roles

"roles" : [
        {
                "role" : "userAdminAnyDatabase",
                "db" : "admin"
        },
        {
                "role" : "clusterAdmin",
                "db" : "admin"
        },
        {
                "role" : "dbAdminAnyDatabase",
                "db" : "admin"
        },
        {
                "role" : "readWriteAnyDatabase",
                "db" : "admin"
        }
]

We are using mongodb x509 authentication mechanism.

My question:

What is this system.views collection?
How can I get my admin user authorised to run command on this collection?

Many thanks.


texts: Problems updating with inner join with multiple on statements

texts:
I have an orders table on a database that contains geographical information about the city and state that the order is from.  This includes latitude and longitude for doing haversine calculations.

I have a new geo-location database containing the latitude and longitude of said city states that differs slightly from my previous database.  This introduces some odd behavior because when I run haversine calculations, mileage will not be calculated correctly due to the slight differences in latitude and longitude from the previous database.

What I need to do is update the latitude and longitude on the orders table to match what occurs in the geo-location database.  This is based off the city and state that is on the order.  I am trying to update my orders table like so:

update ProcurementPortal.orders as orders
inner join
ProcurementPortal.cities_extended as geo
on orders.city = geo.city
and orders.state = geo.state_code
set
orders.lat = geo.latitude,
orders.lon = geo.longitude
where
orders.city = geo.city
and
orders.state = geo.state_code

The problem I am having is that MysqlWorkbench gives me the following error.

  Error Code: 2013. Lost connection to MySQL server during query

Is the syntax I provided correct?  Would it update the orders how I expect it to?  I can run this from the command line, but it runs for a long time and I'm not sure it's really doing what it's supposed to be doing.

I'm fairly new to mysql, so some clarification on how I could accomplish this would be wonderful.

Output of SHOW CREATE TABLE orders - note there are some garbage columns on this.  Needs a lot of cleaning up.  The city / state column I refer to in posted query is oCity and oState:

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `company_id` int(10) unsigned DEFAULT NULL,
  `action_menu` text COLLATE utf8_unicode_ci,
  `oCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  -- many other columns
  `latitude` double(8,2) DEFAULT NULL,
  `longitude` double(8,2) DEFAULT NULL,
  -- more columns
  PRIMARY KEY (`id`),
  KEY `orders_company_id_foreign` (`company_id`),
  CONSTRAINT `orders_company_id_foreign`
    FOREIGN KEY (`company_id`)
    REFERENCES `companies` (`id`) ON
    DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=70120 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Output of SHOW CREATE TABLE cities_extended:

CREATE TABLE `cities_extended` (
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  `zip` int(5) unsigned zerofill NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Running the query in question from the command line runs for a while, then returns the following error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

^ this might be due to the process still running.  I killed the process and it's actually going through now.  Just waiting to see if I get the same 2013 error.


texts: How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

texts:
When I use SQL Server with an int based identity column, I always use the auto increment.  But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.

The naive approach would be to query the data, increment it, and then use that for an insert.

For example:

INSERT INTO myTable
(
  ID,
  Data
)
Values
(
  (SELECT MAX(ID) FROM myTable) + 1,
  "My actual data."
)

But I'm unsure if this is actually the best approach.  Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.

Edit:
Sql Server version 2012.

Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.


texts: Postgres JSON field for many to many relationships

texts:
I'm designing a schema that has a many-to-many relationship as such:

A lead can have several stages and each stage can have several leads. I currently have three tables, with one of them as being a junction table.

stages
----------
id | name | workflow_id | created_at | updated_at

leads
----------
id | name | created_at | updated_at

stage_leads
--------------
id | stage_id | lead_id | created_at | updated_at

(stages belong to a workflow)

I've got indices set up.

**

When a lead moves from one stage to another, a new entry is created in the stage_leads table. My use cases seem simple:

a) find/count all leads on a particular stage, and

b) find/count all stages a particular lead has gone through

My concern is that since every move is creating a new entry into the junction table -- and there are potentially hundreds of stages a lead can go through -- it may lead to hundreds of thousands of rows for very few leads and that could probably impact the lookup time when I'm trying to fetch a specific set.

My question is this: would it be better to use Postgres' JSON functionality to store my data something like this:

stages
----------
id | name | workflow_id | created_at | updated_at

leads
----------
id | name | current_stage_id | stages | created_at | updated_at

... where the stages column on "leads" is a JSON object that logs the stage_ids that the lead has gone through, the timestamps, and a couple of other fields?

This seems to satisfy my use case of finding all leads on a particular stage (I can run a query against the current_stage_id of all leads) and it probably makes retrieving the stages for a particular lead easier. I'm not very confident about it though. I realize I'd be giving up on things like constraints, etc.

Are there any downsides to an approach like this?


texts: Table-valued Data in Common Table Expression

texts:
Most other databases allow you to generate table values using VALUES. As far as I can tell, SQL Server lets you do this with subqueries:

SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
) as sq(givenname,familyname,email);

but I can’t appear to get it working with CTEs. The best I can do is embed a subquery inside a CTE:

WITH data AS (
    SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
    ) as sq(givenname,familyname,email)
)
SELECT * FROM data;

I prefer CTEs because of their readability, but is there a more direct way of doing this? For example PostgreSQL, MariaDB (supporting versions) and SQLite all accept this:

WITH data(givenname,familyname,email) AS (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
)
SELECT * FROM data;


texts: Optimization for table with text and time columns

texts:
I have a simple table with the following columns:

id (int4)
time (timestamp)
name (varchar)
service_name (varchar)

For every service (there are not more than 500 different names), there are a lot of entries. The status varies, but basically only 'ok' is interesting.

I tried to optimize this query:

SELECT service_name, max(id) AS max_id
FROM mytable
where time >= '2019-07-23'
   and status = 'ok'
   GROUP BY service_name;

There exists already an index on the time column. I tried to add another one using the statement:

create index my_index on my_table(time desc, service_name, status);

But that didn't help. PostgreSQL (9.6) doesn't use this index. As I can see the database management system is doing an index scan that consumes most of the time.

Any Idea how I could improve the performance? Thanks a lot!
Right now the query needs more than four seconds. That is a bit too much.

PS

Some numbers: There are currently more than 500 million rows in the table.

PPS:
Added execution plan

HashAggregate  (cost=221449.30..221453.55 rows=425 width=24) (actual time=21852.251..21852.330 rows=409 loops=1)
  Group Key: service_name
  Buffers: shared hit=4202089 read=70842
  ->  Index Only Scan using my_table_time_service_name on my_table hh  (cost=0.70..187312.22 rows=6827415 width=24) (actual time=0.044..13948.546 rows=11192087 loops=1)
        Index Cond: ("time" >= '2019-07-23 20:58:52.886332'::timestamp without time zone)
        Heap Fetches: 4139020
        Buffers: shared hit=4202089 read=70842
Planning time: 0.233 ms
Execution time: 21852.411 ms


texts: Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

texts:
I can ping successfully from my Windows command line to localhost and to 127.0.0.1, but attempts to connect with mysql.exe fail...

I found similar errors but their suggested solutions don't work/apply...

https://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

Host is not allowed to connect to MySQL

Host not Allowed Connectivity to MySQL Server

I'm using a Windows 7 WAMP environment (an old Zend installation) meaning its all local (connections made from localhost/127.0.0.1). All worked fine last Friday when I did an import of a database, today, I get the cannot connect error. I did not overwrite mysql database (or at least, i am pretty sure I did not), so the error has taken me a little by surprise.

C:\Backups\Daily\201607>ping /n 1 localhost

Pinging Silver [::1] with 32 bytes of data:
Reply from ::1: time<1ms

Ping statistics for ::1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>ping /n 1 127.0.0.1

Pinging 127.0.0.1 with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 127.0.0.1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>mysql --user=root
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

C:\Backups\Daily\201607>mysql --user=root --host=localhost
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

My partial extract from my.ini file:

[client]
port=3306
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files (x86)/Zend/MySQL55/"
#Path to the database root
datadir="C:/Program Files (x86)/Zend/MySQL55/Data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server = utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I'm curious on what went wrong - I suspect I'm responsible for it but lack a history file to examine the error of my ways.

Can anyone advise how I can fix and reconnect?


texts: Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

texts:
I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF).

Basically, given a table and data like:

CREATE TABLE `math` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbols` varchar(32) character set utf8mb4 not null,
  PRIMARY KEY (`id`),
  UNIQUE KEY `symbols` (`symbols`)
);
INSERT INTO `math` VALUES (1,'');

(You may not have a font to display the character in the string literal above. It's U+1D542 MATHEMATICAL DOUBLE-STRUCK CAPITAL K)

things look OK:

mysql> select * from math;
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

mysql> select * from math where symbols = '';
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

So far so good. But then there's this crap:

mysql> select * from math where symbols = '';
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

and

mysql> INSERT INTO `math` VALUES (2,'');
ERROR 1062 (23000): Duplicate entry '?' for key 'symbols'

(The string literal above has U+1D543 MATHEMATICAL DOUBLE-STRUCK CAPITAL L. Note that MySQL's error message has a ?, but the U+1D542 in the results of the SELECT above does dispaly correctly for me, so there don't seem to be encoding issues as far as IO with the server.)

(Code above updated; it originally had 1 for the primary key, which fails for obvious reasons.)

Screenshot for those with font issues:

So, MySQL thinks these two characters are the same? I know it case-folds, but this isn't a matter of casing.

Needless to say, I didn't even realize I had this problem till it came up in production, because the real-world data involved rarely differs on only these characters. However, this is totally unacceptable collation behavior.

Switching to the binary collation does fix it, however I'm using Django to access the database, and when I use a binary collation it then gives me bytes instead of characters (I can decode them myself, but that's a big pain).

I'm guessing the issue has something to do with these character being outside the BMP, but it still surprising bad behavior.

Is there a way to get MySQL to use sensible collation, short of writing and installing one myself?

I suspect the non-BMP characters are the problem, since I also tried:

⛇ (U+26C7 BLACK SNOWMAN) (a BMP character) works fine.
 (U+1F300 CYCLONE) (a SMP character) gives the same error as above
 (U+1F0A1 PLAYING CARD ACE OF SPADES) (SMP) same error
 (U+20003 CJK UNIFIED IDEOGRAPH-20003) (SIP) same error

(I can't link these to codepoints.net, since I don't have enough reputation. Should be fairly obvious what their URLs are though.)

I'm using MySQL 5.5.40 on Ubuntu 14.04.


texts: Can queries that users ORDER BY using date to sort data be non deterministic?

texts:
I have a table in my database where I store dates using the following standard: YYYY-MM-DD. As you can see, I'm not saving the hour. The first time I exported the same table to a csv file I got the following records in this order:

AAA@outlook.com
BBB@gmail.com
CCC@gmail.com
DDD@hotmail.com

But when I ran the same query to export the data to the same CSV, I received the records in this following order:

AAA@outlook.com
111@hotmail.com
222@gmail.com
333@gmail.com

So, the conclusion that I reached is when sorting data as this can be non deterministic. Isn't? This is my query to export the data from my database:

SELECT id AS "_id", cpf, email, name, agency, account, "createdDate" FROM "NextUsers", "Users" WHERE "userId"="Users".id ORDER BY "createdDate" OFFSET $1 LIMIT 500;

Where $1 starts at 0 and it is incremented with 500 per iteration.
OBS: The returned data from both queries are correctly sorted but not following in the same order as you can see.


texts:
SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM
                (SELECT user_id
                       FROM (
                                SELECT sender_id AS user_id
                                FROM `connections`
                                WHERE receiver_id=users.id AND status='2'
                                UNION
                                SELECT receiver_id AS user_id
                                FROM `connections`
                                WHERE sender_id=users.id AND status='2'
                        ) tempUser
                        WHERE user_id IN (
                                  SELECT sender_id AS user_id
                                  FROM `connections`
                                  WHERE receiver_id='4' AND status='2'
                                  UNION
                                  SELECT receiver_id AS user_id
                                  FROM `connections` WHERE sender_id='4' AND status='2')
                         GROUP BY user_id)
                as mutualConnection)

     FROM users

Error:

  #1054 - Unknown column 'users.id' in 'where clause'

How to use pass value to sub query


texts: Can't find process that is creating backups

texts:
We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine.

The SQL Server log shows there is a full backup every day at 7 PM but we can't find evidence of any plan scheduled in SQL Server.

The technicians we contacted couldn't tell us if there was some sort of automatic backup, all they told is that the entire machine is being backup up.

Using a script I found in this forum thread I found out that the physical device name is a GUID and that this means that this is an external backup process:

USE [msdb]
GO
SELECT
    [bs].[database_name],
    [bs].[backup_start_date],
    [bs].[backup_finish_date],
    [bs].Server_name,
    [bs].user_name AS [BackupCreator] ,
    [bmf].physical_device_name
FROM msdb..backupset bs
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC

This is a row from the above query:

  db_name 2015-09-16 19:01:23.000   2015-09-16 19:01:28.000SERVER_NAME  NT AUTHORITY\SYSTEM{424F084A-F35D-4A66-8FC7-072268A89A77} 5

Moreover the backup start and finish date spans only for 5 seconds, so I guess it's clear it's not a job of sql server.

This is a line from the log:

  2015-09-03 19:02:30.71 Backup
  Database backed up.
  Database: db_name, creation date(time): 2012/10/12(20:52:11), pages dumped: 3290021,
  first LSN: 276028:152755:172, last LSN: 276028:152827:1, number of dump devices: 1,
  device information:
  (FILE=1, TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).
  This is an informational message only. No user action is required.

So, since they can't help us what can I do to track down the process responsible of the backups? We need this because we want to coordinate the backup in an ordered manner, and do the transaction log backups too in order to keep the log at a reasonable size (now we do shrink the log every week, and this is not the good way to go).