texts: How to detect in which schema a procedure is called?

texts:
I'm struggling with a problem in Oracle 12.1 regarding to procedures.

We are using a data isolation approach based on schemas. We use one schema as PUBLIC and each project has exactly the same tables and procedures, but different data.

Problem:
I need to get the schema name where the procedure was called in order to update a row for a table in the PUBLIC schema that holds all the different schemas.

Header definition

CREATE OR REPLACE PACKAGE PKG_DONE
AUTHID DEFINER
AS
  PROCEDURE ACK_FROM_WEB(UUID INTERNAL.UUID%TYPE, USERNAME PUBLIC.USERS.USER_NAME%TYPE);

END PKG_DONE;

Body definition

CREATE OR REPLACE PACKAGE BODY PKG_DONE AS
  PROCEDURE ACK_FROM_WEB(P_UUID INTERNAL.UUID%TYPE, P_USERNAME PUBLIC.USERS.USER_NAME%TYPE)
  AS
  BEGIN
    INSERT INTO ACK_WEB_USER(UUID,USERNAME) VALUES (P_UUID,P_USERNAME);
    UPDATE PUBLIC.ACK_CONFIRMATIONS SET ASSIGNED = P_USERNAME WHERE UUID = P_UUID AND SCHEMA_NAME = USER;
  END ACK_FROM_WEB;

END PKG_DONE;

It works perfectly in our website as we set the session with the SCHEMA when operating, but now we need external confirmation, via email and it only works with PUBLIC schema for security reasons. I would like to retrieve the schema where I call the procedure instead of USER in the statement UPDATE PUBLIC.ACK_CONFIRMATIONS SET ASSIGNED = P_USERNAME WHERE UUID = P_UUID AND SCHEMA_NAME = USER;.

Is there any command/dbms procedure or anything where I can get it?


texts: How to store iterations of similar datasets

texts:
I have a dataset that is stored across 20 or so tables, most of which have a few million rows.  This data will be generated fresh each month, and compared to past datasets.

I'm pretty new to dealing with datasets of this size, so I'm unsure what would be good practice in storing these past datasets.  I need the data to still be accessible to queries, so dumping the database to backup files won't be enough.  Is there some feasible way to move each dataset into a new database once I get a new one?  Or should I just put timestamps on each row and keep everything in one giant dataset?

Sorry if this is too vague, I'm not sure how exactly to ask this question. I'd be open to changing it if there are any suggestions.


texts: How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?

texts:
Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute.

Your expert advice: How can I write the C# insert code for SQL Server, such that there is a guarantee that SQL caches everything immediately in its own RAM, without blocking my app for more than it takes to feed data into said RAM? To achieve this, do you know of any patterns for setup of the SQL server itself, or patterns to set up the individual SQL tables I'm writing to?

Of course, I could do my own version, which involves constructing my own queue in RAM - but I don't want to reinvent the Paleolithic Stone Axe, so to speak.


texts: Select from multiple rows without duplicate values, with all random data

texts:
I have a table that gets results from three different sources. Each column represents a source, and each row a result of an outcome. There are over 50k rows for a total of 150k results.

I need to run a report that within these results, I want to remove duplicates leaving the unique values behind, in their respective columns. The majority of the results will all be duplicates, and I would assume around ~500 are unique.

The other 'remove duplicate from multiple columns' posts haven't worked for me; any combo of distinct, groups, and unions I have not been able to get to work.

Example of data below. Thanks.

Raw Data:

Expected Results:

Squiggles:


texts: Stored Procedure which accept xml file as input

texts:
<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
   </book>
</catalog>

I have xml file as above.

In my SQL Server 2008 database there is table Book__Master In which all the columns are same as elements in above xml file i.e. there are columns like bookid, author, title etc.

Now I want to write a stored procedure which accepts this xml file as input parameter and stores the element's values as it is in their respective columns.

Can anyone help me on this that how I can write this type of stored procedure?


texts: Is there a way to replicate Postgres databases in Amazon RDS between regions?

texts:
We have a Postgres RDS instance in the AWS Oregon Region.  For some supposed legal reasons, we have to host our client's European data in the Ireland AWS Region, but we need that data synchronized to the Oregon Region.  We will probably have to have an instance in the Singapore Region eventually, too.

We would like the databases to all be synchronized across regions.

Is this something that can even be done with Postgres RDS instances, or is MySQL the only database in RDS that supports this concept of replication between regions?

Any input on this matter would help.  We really enjoy how RDS makes administering the databases so easy, but if RDS can't allow us to sync databases between regions with Postgres, then we'll have to switch back to EC2 Linux instances with Postgres.


texts: Distributed Availability Group Direct Seeding FAILED, failure_state SQL Error, failure_state 2

texts:
We just started setting up Distributed Availability groups to replicate our production databases into a new reporting cluster. The first availability group that we setup for replication worked great without any issues, however when we then moved on to the second availability group with much larger databases (over 3TB total) it took much longer and two of the 5 databases failed. We setup the distributed availability group to use direct seeding and when querying the sys.dm_hadr_automatic_seeding table it indicates the current_state as FAILED, with failure_state 2 (SQL Error) or 21 (Seeding Check Message Timeout):

What can we do to troubleshoot this issue?


texts: How do I identify all the students who have passed?

texts:
Suppose I have a Table student and a Table exam which tracks all the exams a student has taken, how do I find all the students who have passed or not taken any exams similar to a white-list? In this example that means students 1 and 2.

PS: pass flag U means that the status is unknown, active flag N means the exam was removed, rest of the flags are self explanatory.

Just to be very clear, the real use case is actually assessments, I need to figure out people who have no pending assessments to participate in, so that they can proceed with next set of procedures. Others would need to finish the pending items.

+-------------+------+
| student_idn | name |
+-------------+------+
| 1           | Mark |
| 2           | Jack |
| 3           | Jane |
| 4           | Jill |
+-------------+------+

+----------+-------------+------+--------+
| exam_idn | student_idn | pass | active |
+----------+-------------+------+--------+
| 1        | 1           | Y    | Y      |
| 2        | 1           | Y    | Y      |
| 3        | 1           | Y    | Y      |
| 4        | 2           | Y    | Y      |
| 5        | 2           | N    | N      |
| 6        | 3           | Y    | Y      |
| 7        | 3           | Y    | Y      |
| 8        | 3           | Y    | Y      |
| 9        | 3           | N    | Y      |
| 10       | 4           | U    | Y      |
+----------+-------------+------+--------+

I came up with this below query but is there a better way to do this? I feel that this is O(n) or O(nlogn) depending on index etc not sure (anyone knows the best possible for this) hence should be close to optimal?

SELECT
  *
FROM student
WHERE NOT EXISTS (SELECT
  *
FROM exam
WHERE exam.student_idn = student.student_idn
AND pass <> 'Y'
AND active = 'Y')


texts: SQL Server 2012 Query: Invoices with Earliest Date in State for each Vendor

texts:
I have the tables:

Invoices ( VendorID, InvoiceDate, InvoiceNumber, InvoiceTotal,...),
Vendors (VendorName, VendorID,....)

I am trying to obtain a list of the earliest invoices for each vendor, together with VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal (for that earliest date).

Here is what I have :

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID
WHERE  InvoiceDate   <= ( SELECT  Min(InvoiceDate)
                          FROM Invoices
                          JOIN Vendors ON V.VendorID=Vendors.VendorID  )
GROUP BY VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal

The problem is that I am getting only one invoice, which is the earliest of all invoices, not the earliest by state. What is going on?


texts: SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

texts:
I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps.

This seemed to work, and I verified that the records are in the new msdb tables.

Once I restart SQL Agent, I only see some of those jobs listed under SQL Server Agent in SSMS.  I am very puzzled why SOME are not listed.

I am using SQL Server 2008 R2.


texts: Can't export table structure with expdp, parameter full=X conflicts with tablespaces=X

texts:
I'm trying to export table schema with expdp,
expdp \'sys/XXXX as sysdba\' dumpfile=XXX.dmp logfile=XXXX.log content=METADATA_ONLY tablespaces=XXXX

And it fails with
ORA-31655: no data or metadata objects selected for job

Then I added full=Y and it conflicts with tablespaces parameter:
ORA-31655: no data or metadata objects selected for job

How can I export all schemas from specific tablespaces only?


texts: CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4

texts:
We had an issue with CDC where the capture job failed and wouldn't restart itself.  It was in a DEV environment so no harm but has anyone seen these errors or know what exactly went wrong with CDC?

SQL 2014 EE 12.0.5540 - 2 node AG with SSISDB

  Procedure or function sp_batchinsert_1663605265 has too many arguments
  specified. For more information query the sys.dm_cdc_errors dynamic
  management view

  The Log-Scan Process failed to construct a replicated command from log
  sequence number (LSN) {00002d0d:0000f11f:0002}. Back up the
  publication database and contact Customer Support Services. For more
  information

  Log Scan process failed in processing log records. Refer to previous
  errors in the current session to identify the cause and correct any
  associated problems. For more information query the sys.dm_cdc_errors
  dynamic management view

We attempted to drop and recreate the capture job using sp_cdc_drop_job abd sp_cdc_add_job.  We also attempted failing over to the other node as well as server/service restarts.  Nothing seemed to let CDC restart itself.

We applied CU4 on Friday through AG rolling patching so we are on the latest and greatest.  This issue cropped up right after we patched and failed over.

Added Log query result


texts: How to estimate PostgreSQL reindexing time?

texts:
How do you estimate the total time reindexing an entire table will take in PostgreSQL?

I have a large PostgreSQL database, the primary table of which has 15 million records and 100 columns, and 200 indexes.

I'm seeing a problem where, after a night of processes loading data into the table, queries to the table are terribly slow for about 6 hours after the loading ends. However, these queries miraculously become fast again after 6 hours post-load.

Is this temporary slowness caused by Postgres updating indexes in the background?

I was thinking about running a REINDEX on the table immediately after loading ends, but I'm not sure how long this will take, and I'm worried it might take longer than the original load and will only worsen performance.


texts:
I have two tables a and b. A contains start dates and end dates whereas table b contains 10 min bins with values associated to each bin:

a
StartDate            EndDate
2018-03-06 10:13:00  2018-03-06 10:45:00

b
TimeStamp / Amount
2018-03-06 10:10:00 / 100
2018-03-06 10:20:00 / 89
2018-03-06 10:30:00 / 50
2018-03-06 10:40:00 / 78

The result I'd like to acheive is this:

a.StartDate / a.EndDate / b.Amount
2018-03-06 10:13:00 / 2018-03-06 10:45:00 / 248.0

where b.Amount is equal to = 0.7 * 100 + 89 + 50 + 0.5 * 78

I've started the below query but it's not giving me any results:

SELECT a.StartDate, a.EndDate, b.Amount
FROM a
JOIN b ON a.StartDate > b.TimeStamp
       AND a.EndDate < DATEADD(mm,10,b.TimeStamp)


texts: Query using $gte and New Date() working in MongoDB shell but not through python pymongo

texts:
When I run the following in the mongodb shell, it returns the correct number of records:

(searching for the matching "SourceName" within the last 8 days)

mongos> db.fdl.count({ SourceName : "SRC1" , "Created" : { $gte : new Date(new Date().setDate(new Date().getDate()-8))  }})
482

But when I try to run it through python / pymongo, it returns nothing:

import time
import pymongo

myclient = pymongo.MongoClient("mongodb://mylogin:mypass@mymongo.mydomain.net")
mydb = myclient['fdl']

print mydb.fdl.count({ SourceName : "SRC1" , "Created" : { $gte : new Date(new Date().setDate(new Date().getDate()-8))  }})

myclient.close()

then run it:

python query.py
0

In the python code, if I run the query:

    print mydb.fdl.count({ SourceName : "SRC1" })

Then I do get a value. So the problem comes from the $gte search.

The problem is not with the count() method, because if I use the find() method, the problem is the same: documents returned in the MongoDB shell, but nothing when calling with python.

Any help would be greatly appreciated !
Thanks a lot in advance :-)


texts: MySQL slave seems confused as to just how far out of date it is

texts:
I have a slave that claims, in SHOW SLAVE STATUS, to be zero seconds behind the master.

However, it also says it's on Master_Log_File binlog.009636, which is 46.9 gig behind where the master is. I can look at the database and see the tables are all out of date.

No replication-related queries are running; the threads say "Waiting for master to send event" and "Has read all relay log; waiting for the slave I/O thread to update it." Yet the log IS moving, as it's now 46.7 gig behind.

What is going on here? Server is MySQL 5.0.95.


texts: Is moving large columns to separate table likely to have significant performance improvement?

texts:
We have a large table that registers Request and Response, and captures other data that is related to that request. We are looking to improving performance. One of the suggestions is to move these larger columns (request and response) to separate tables so they would only be accessed when they actually need to be read and not every time when a record is being looked up.

Is moving large columns to a separate table likely to yield a significant performance improvement?


texts: pt-online-schema-change error creating triggers

texts:
I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ?

pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING BTREE" D=master,t=website,u=root --execute --ask-pass
Enter MySQL password:
No slaves found.  See --recursion-method if host inoopa-storage has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `master`.`website`...
Creating new table...
Created new table master._website_new OK.
Altering new table...
Altered `master`.`_website_new` OK.
2020-06-05T16:02:10 Creating triggers...
2020-06-05T16:12:19 Dropping triggers...
2020-06-05T16:12:19 Dropped triggers OK.
2020-06-05T16:12:19 Dropping new table...
2020-06-05T16:12:19 Dropped new table OK.
`master`.`website` was not altered.
Error creating triggers: 2020-06-05T16:12:19 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TRIGGER `pt_osc_master_website_del` AFTER DELETE ON `master`.`website` FOR EACH ROW DELETE IGNORE FROM `master`.`_website_new` WHERE `master`.`_website_new`.`id` <=> OLD.`id`"] at /usr/bin/pt-online-schema-change line 11074, <STDIN> line 1.


texts: Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table - DDL

texts:
I have an issue with my SQL syntax when creating a table with a set of Foreign Key (Orig_Dept_ID & New_Dept_ID) that references Dept_ID in the Department Table.

Below is my sql syntax:

CREATE TABLE DEPT_TRANSIT
    (Orig_Dept_ID int,
    New_Dept_ID int,
    CONSTRAINT department_fk foreign key (Orig_Dept_ID, New_Dept_ID)
             REFERENCES Department(Dept_ID)
    );

Could someone please advise on how I should go about doing this?

Thanks alot!


texts: How to design e-commerce database for a product which has an option what time to get

texts:
I'm designing an e-commerce website.

It has a product that have options to choose for a customer.

the options that a customer can select are what time to receive the product today.

-------------
|    11:00   | <-- select box on product page on website
-------------
|    15:00   |
-------------
|    18:00   |
-------------

So I want to create a product with options

11 o'clock for Monday and Thursday
3 o'clock for Everyday
6 o'clock for Tuesday and Friday

What I have done so far with this idea is

-------------      ---------------------     --------------------
|   product  |     | product_schedule   |   |product_days_of_week|
-------------      ---------------------     --------------------
|    id      |     |    allow_time      |   |    day_of_week     |
-------------      ---------------------     --------------------
|    name    |     |     product_id     |   | product_schedule_id|
-------------      ---------------------     --------------------

Is there any better design for this purpose?
Thanks


texts: Count how many records belong to 2 categories in a n to n relation

texts:
I have a database containing articles whose schema is more or less the following:

COLLECTIONS ( PK id )
> 1 : n >
TOPICS ( PK id, FK collection_id )
> n : n >
TOPICS_TO_ARTICLES( FK topic_id, FK article_id )
> n : n >
ARTICLES ( PK id)

What I need is to find a way to understand how articles and collections are related, and one of the metrics is "how many articles belong to "collection 1" AND "collection 2". I thought I found a working query, but I think I'm missing something. Here is my query:

SELECT
  COUNT(*) AS total
FROM
  topics_to_articles AS TTA
INNER JOIN
  topics AS T ON T.id = TTA.topic_id
INNER JOIN
  articles AS A ON TTA.article_id = A.id
WHERE
  T.collection_id = cat_1_id AND A.status = 1 AND TTA.article_id IN(
  SELECT
    TTA2.article_id
  FROM
    topics_to_articles AS TTA2
  INNER JOIN
    topics AS T2 ON T2.id = TTA2.topic_id
  INNER JOIN
    articles AS A2 ON TTA2.article_id = A2.id
  WHERE
    T2.collection_id = cat_2_id AND A2.status = 1
)

I was pretty sure about this query being ok, but I noticed that if I invert the two "collection_id" variables (like c1 = 1 and c2 = 2 and then c2 = 1 and c1 = 2) I obtain two different results.

Is there a way to get that info?


texts: MySQL InnoDB restoration from hardware failure

texts:
One of my server went down due to old hardware failure on a old Dell server that's over 10 years old. I was able to recover the files and got it to a local machine.

I want to restore a MySQL instance from InnoDB storage engine.

I have ib_logfile0, ib_logfile1, ibdata1 and *.frm files. How would I go about it? It would conclude restoring the databases, including the mysql database and all the other databases that were already there? how would I go about it?


texts:
I have these two table here in ssms:

one is called client table, which have a column called city, and it is really city name, like new york, etc.
Another table is called city table, it has city's ID and city name. such as CityID:1 City: New york.

So in my Client table, I know how to create another column called CityID, but how can I populate the CityID column in the client table, entering them by searching the CityID in the City table?


texts: What is a proper way to handle a "sequence number" like field?

texts:
The fields of a trimmed-down version of a database I'm working with looks like this:

Model Number, Model Name, Customer, Date, SeqNum

The customer can purchase the exact same item on the same date so we're going to use a sequence number to distinguish the rows. So entries might look like:

AS-23423, Box, John Doe, 01/22/2013, 00
AS-23423, Box, John Doe, 01/22/2013, 01
AS-23423, Box, John Doe, 01/22/2013, 02
AS-23445, Pen, John Doe, 01/22/2013, 00
AS-23454, Case, John Doe, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 01

How should you handle the sequence number if an application has to be CRUD compliant? I've considered doing MAX(SeqNum) + 1 upon inserting but this seems off especially when handling update and delete, so I figured there might be a better way.


texts: Permissions needed for dbms_metadata.get_ddl in a function

texts:
Oracle Enterprise 11.2.0.3

I'm receiving:

ORA-31603: object "string of type "string" not found in schema "string"

when executing a function that calls dbms_metadata.get_ddl.

I know that procedures/functions need have to have the permissions granted explicitly instead of through a role: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

I also know that the permissions needed to execute dbms_metadata.get_ddl is in the SELECT_CATALOG_ROLE role: https://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/

However, the SELECT_CATALOG_ROLE role contains over 2400 permissions.  So, what are the explicit permissions needed to execute dbms_metadata.get_ddl so that I can call it from a function?

Test Case:

create or replace function getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

create table utilities.mytable (mycol varchar2(1));

select dbms_metadata.get_ddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
ORA-31603: object "MYTABLE" of type TABLE not found in schema "UTILITIES"

EDIT

So far, I have explicitly granted the following to the user that is owns getddl():

select any table
select any dictionary


texts: How to create oracle linked server in oracle server itself?

texts:
Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit).

  I have access to remote database but only read only

  I can't create views and stored procedure.

  I want to create linked server and with it's help create views in my
  local oracle database.
  Is this feasible and is the speed of query
   execution comparable?


texts: MySQL - "Visual Studio must be installed" - but it is?

texts:
When using the MySQL installer, it says "Visual Studio version 2015, 2017 or 2019 must be installed".
I have installed Visual Studio 2019 and "MySQL for Visual Studio", but it still says, "the requirement is still failing".
I should point out that I installed the 32-bit version of MySQL for Visual Studio, because I couldn't find a 64-bit version.  Is this relevant?
What would cause this and how do I fix it?


texts: Writing a query to COUNT all the services involving a particular

texts:
Learning PHP and SQL on an online MOOC, this is a homework that I've got to do.

I need to write a query in SQL to count all the services which involve a particular person in my database.

The particular's name is Gary Crowley located in the 'staff' table.
Below is my table:

My question is: What is the actual SQL query I should write to get the exact count of all the services which involve Gary Crowley? As you can see in the picture, the name appears four times. I have written a query that output the services only involving Gary Crowley but I don't know how to make the COUNT query.


texts: Mongodb update conditional set operator ($set only when it is insert )

texts:
Is there anything in Mongo update where $set when will work only when an insert is done.

example is  I have a collection which has these fields:

Username
FirstOccurrence
Count

What i want that i will run a update operation like this.

coll.update{ q: {username} ,u: {$set:{FirstOccurrence},$inc:count} }

But condition will be if UserName is already existing then it wont run the $set it will just increment the count.

Is it possible to do it I am running Mongo 2.6.4 with Java driver 2.13.0

Regards
Viren


texts: Temporarily splitting a SQL Server cluster

texts:
I currently have a pair of database servers up and running separately, but with the same structure and (hopefully) the same data on both of them. The applications that utilize the data from these servers have extra behavior set up in them such that every statement is run against both servers; this has (so far as we know) ensured that the data on the two servers continues to match up correctly, with some minor issues here and there when a configuration is changed incorrectly.

Currently, we have the two servers specifically for the case of hardware failure, and for disaster recovery; working out of Florida, we pack up half of our department's hardware whenever a hurricane starts to look like it's headed our way. Unfortunately, the half that leaves the state needs to continue functioning, and is not reconnected via VPN (we're trying to get that to happen).

So with that all in mind, is there any way with SQL Server (current release or otherwise) that we can set up a cluster that allows half of the nodes to just vanish and begin acting as a separate cluster? And more importantly, is there any known sane way for the two clusters to resolve any inconsistencies once they're both back on the same network? With certain tables exempted from this and requiring manual intervention, our current strategy is to truncate everything on the server that left, trusting the on-site server to hold the accurate data.


texts: How do I handle the "Invalid field size for datatype" when using bcp?

texts:
I'm trying to get a database into Azure SQL Database using bcp, but first I'm trying to get it to load into a local database on my dev machine. I've only used bcp once before, years ago, so I'm a newbie at it. I scripted the original database, then created the same database on my machine, with all of the tables empty. Then I used bcp to export one of the tables into a native file. Next, from PowerShell I issued this command:

bcp CoreFramework.APP.ExampleTable IN .\ExampleTable.bcp -T -n

That resulted in the following error:

  Starting copy... SQLState = S1000, NativeError = 0 Error =
  [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for
  datatype

  0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)
  Total     : 1

I suspect the problem is one of the columns in this table. The ExampleTable has several data types in it, as an example of using those data types. Here's the definition:

CREATE TABLE [APP].[ExampleTable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ForeignID] [bigint] NULL,
[RequiredNvarchar] [nvarchar](120) NOT NULL,
[UniqueNvarchar] [nvarchar](120) NULL,
[TinyIntField] [tinyint] NULL,
[SmallIntField] [smallint] NULL,
[IntField] [int] NULL,
[BigIntField] [bigint] NULL,
[SmallMoneyField] [smallmoney] NULL,
[MoneyField] [money] NULL,
[RealField] [real] NULL,
[FloatField] [float] NULL,
[DecimalField] [decimal](10, 2) NULL,
[NumericField] [numeric](10, 2) NULL,
[BitField] [bit] NULL,
[DateField] [date] NULL,
[TimeField] [time](7) NULL,
[SmallDateTimeField] [smalldatetime] NULL,
[DateTimeField] [datetime] NULL,
[DateTime2Field] [datetime2](7) NULL,
[NcharField] [nchar](50) NULL,
[VarcharField] [varchar](50) NULL,
[CharField] [char](50) NULL,
[VarcharMaxField] [varchar](max) NULL,
[NvarcharMaxField] [nvarchar](max) NULL,
[VarbinaryField] [varbinary](max) NULL,
[Created_By] [varchar](25) NOT NULL,
[Created_On] [datetime] NOT NULL,
[Updated_By] [varchar](25) NULL,
[Updated_On] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[UniqueNvarchar] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

My guess is the problem is with NvarcharMaxField and VarbinaryField. How do I use bcp to get this data into the table on my machine?


texts:
I am moving records from one database to another, as a part of archiving process. I want to copy the rows to destination table and then delete the same rows from the source table.

My question is, what is the most efficient way to do a check if the first insert was successful before deleting the rows.

My idea is this, but I feel there is a better way:

@num_records=select count(ID) from Source_Table where (criteria for eligible rows)

insert * into Destination_Table where (criteria for eligible rows)

if ((select count(ID) from Destination_Table where (criteria) )=@numrecords)

delete * from Source_Table where (criteria)

Is it better/possible to combine it with RAISERROR function? Thank you!


texts: What type of files are C-9999-yyyymmdd-00 in Oracle

texts:
At my {ORACLE_HOME}\database I found like 3GB of this type of files:

I think they are not backup of controlfile because size of files changes over time.


texts:
I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it is database specific, i.e. I have cloned 9 successfully but 1 with this issue. All the schemas would be owned by dbo.

Because I can't get this reproduced, why were certain schemas not be created in the clone, would anyone have any idea what to check or if there is a way to debug the dbcc command?

I'm using SQL Server 2016 SP2 CU1.

The database was originally created before SQL Server 2005. If I go select name, schema_id, schema_name(schema_id) SchemaName, * From sys.objects in certain objects the schema name is null and the schema id isn't in sys.schemas. A few tables and synonyms are effected, and I can't twirl down either the tables node or synonyms node in object explorer of ssms (killed session error!).  Also to clarify this is only an issue in the cloned database. I also tried recreating the synonym and transferring ownership of the table but I get an error saying the table can't be found.


texts: Is there a way to compare very short periods of activity in Oracle?

texts: I am having a problem with an application that requires very low latency (less than 100 ms per txn.)  Every once in a while we will see spikes where transactions take 20-30 SECONDS.  We looked at the ASH report and we are seeing "log file sync" waits, but we are unclear on the exact source of the problem.  I want to compare this problem period with a normally operating period.  However, the only way I know of to do this is using AWR compare period reports.  The problem with this is that the periods are too long (30 mins).  I need some way to compare very short periods of time after the problem has occurred since I have now way of knowing when it will happen.  Any advice would be appreciated.


texts: Splitting columns and getting result as a JsonArray in Postgresql

texts:
I have a table like this:

Time  | id/type  | value

1        A/a       10
2        A/b       15
3        A/c       8
4        A/b       2

I need to get a Json Array in the format:

[[Time, value for a, value for b, value for c]]

For example: for this table

[[1,10,Null,Null], [2,Null,15,Null],[3,Null,Null,8],[4,Null,15,Null]]

Could you please help me on this problem? I am quite new to Sql in general, so any help is appreciated.

I have made some attempts as follows:

First convert it into a new_table with layout:

Time  | id  | a  | b  | c

1      A   10    Null   Null
2      A   Null  15     Null
3      A   Null  Null    8
4      A   Null  2      Null

and then use

with new_table as (
select "Time", split_part("id/type", '/', 1) id,
  case when split_part("id/type", '/', 2) = 'a' then "value" end a,
  case when split_part("id/type", '/', 2) = 'b' then "value" end b,
  case when split_part("id/type", '/', 2) = 'c' then "value" end c
from tablename)
select array_to_json(array_agg(Array[new_table."Time",new_table.a,new_table.b,new_table.c])) from new_table;

Using this code in new_table gives the result I expected, but I am not sure if it is the right approach to create a new table every time we need such JsonArray.

Thank you!!


texts: Query returns repeating arrows when getting sum of a column grouped by some others columns

texts:
I have table Inventory and I want to get the sum(quantity) grouped by the columns gid, version, warehouse

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    100   +    name 1    +
|   gid1   +    1     +    1      +    200   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

The following query works, as it returns 2 records with the sum quantity:

SELECT gid, version, warehouse, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse;

+----------+----------+-----------+----------+
|   gid    | version  |  warehouse| quantity |
+----------+----------+-----------+----------+
|   gid1   +    1     +    1      +    300   +
|   gid2   +    1     +    2      +    200   +
+----------+----------+-----------+----------+

I tried to add also the column description in the records:

SELECT gid, version, warehouse, SUM(quantity) as total, description
FROM Inventory
GROUP BY gid, version, warehouse;

but I got an error message:

  Column 'description' is invalid in the select list because it is not
  contained in either an aggregate function or the GROUP BY clause

I searched it and I ended up with this query:

SELECT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse
) T2
ON T1.gid = T2.gid

It shows the correct sum, but it returns 3 records :

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid1   +    1     +    1      +    300   +    name 2    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

How can I get only the 2 arrows? The following is the desired output:

+----------+----------+-----------+----------+--------------+
|   gid    | version  |  warehouse| quantity | description  |
+----------+----------+-----------+----------+--------------+
|   gid1   +    1     +    1      +    300   +    name 1    +
|   gid2   +    1     +    2      +    200   +    name 2    +
+----------+----------+-----------+----------+--------------+

My database is in SQL Server Express 2008.


texts: How can I append a column to an inner SQL statement?

texts:
I have two tables that have the same structure except one has an additional column that should be auto-incremented.  Sequence and trigger are defined.

I want to copy all data from one table to another using this SQL statement:

INSERT INTO A_WITH_ADDITIONAL_COLUMN
SELECT * FROM A;

But I'm getting error:

SQL Error: ORA-00947: not enough values

Which is fine because there is one more column in the target table.

How do I append an additional NULL at the beginning of every row of the source table in order to be able to fill the target WITHOUT enumerating all columns of the source table in the query?


texts: How to find the statistics level of a column in postgres?

texts:
I have altered the statistics level of column using below command.
edbstore=> alter table orders alter column orderdate set statistics 100;
From which table, i can find the statics level of all the columns in postgres db.


texts: If a Parallelism Exchange Event deadlock is victim-less, is it a problem?

texts:
We're seeing a lot of these Intra-Query Parallel Thread Deadlocks in our Production environment (SQL Server 2012 SP2 - yes...I know...), however when looking at the Deadlock XML that has been captured via Extended Events, the victim-list is empty.

<victim-list />

The deadlocking appears to be between 4 threads, two with the WaitType="e_waitPipeNewRow" and two with the WaitType="e_waitPipeGetRow".

 <resource-list>
  <exchangeEvent id="Pipe13904cb620" WaitType="e_waitPipeNewRow" nodeId="19">
   <owner-list>
    <owner id="process4649868" />
   </owner-list>
   <waiter-list>
    <waiter id="process40eb498" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe30670d480" WaitType="e_waitPipeNewRow" nodeId="21">
   <owner-list>
    <owner id="process368ecf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process46a0cf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe13904cb4e0" WaitType="e_waitPipeGetRow" nodeId="19">
   <owner-list>
    <owner id="process40eb498" />
   </owner-list>
   <waiter-list>
    <waiter id="process368ecf8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Pipe4a106e060" WaitType="e_waitPipeGetRow" nodeId="21">
   <owner-list>
    <owner id="process46a0cf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process4649868" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>

So:

The Victim List is empty
The application running the query does not error and completes the query
As far as we can see, there is no obvious issue, other than that the graph is captured

Therefore, is this anything to worry about other than noise?

Edit: Thanks to Paul's answer, I can see where the issue likely occurs and appears to resolve itself with the tempdb spill.


texts: SQL Server Windows authentication: VMs versus others

texts:
This really seems like a weird one to me. I have two VMs of interest, one is running Win2k8 R2 x64 and the other is running Windows 7 X64. The server is hosting SQL Server 2k8 while the latter has SQL Server Management Studio installed. Neither of the VMs are joined to a domain but are part of the same workgroup.

In the past I've always been able to use Windows authentication with SQL Server 2k8. As long as I made sure that the account I was using on the Windows 7 VM was duplicated, ID and PW exactly on the server, it worked just fine.

Here's the interesting bit. If I try to connect to the same SQL Server instance using SQL Server Management Studio (or any other tool really) from the physical machine that's hosting the VMs, or any other physical machine for that matter, the connection is refused with the untrusted domain error.

FWIW, the networking on both VMs is set to bridged, and they have valid IP addresses on my local segment (192.168.1.x) that can be seen and otherwise accessed by every physical machine I have. And the physical machines I'm using are set to the same workgroup name as the VMs.

I'd love to know why authentication works on VMs in the workgroup but doesn't work on the physical machines. Can anyone offer any clue?


texts: Average Waiting Tasks Count / Resource Waits

texts:
I'm trying to help troubleshoot a sudden performance problem with SQL Server 2014 SP2 (12.0.5659.1). I don't have details on what exactly the scheduled stored procedures are doing, but I know that the SP(s) with problems are using OLEDB and remotely querying a SQL server to refresh local tables. Execution that used to run for less than an hour, now takes days, basically never completing. We've verified that no physical network issue exists.

Looking at the Activity Monitor the things that stand out are:

Waiting Tasks averaging 9 or 10.
Network I/O and SQLCLR both have Wait Times in the 2000-4000 ms/sec
with avg Waiter counts of 3 or 4.
The Cumulative Wait Times on both SQLCLR and Network I/O are in the
neighborhood of 500K seconds.
Latch also looks pretty high 2600 ms/sec, waiter count at 3-5.

Are these way out of line or am I barking up the wrong tree?


texts: Why do linked servers have a limitation of 10 branches in a CASE expression?

texts:
Why does this CASE expression:

SELECT CASE column
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        ... c -> i
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END [col]
FROM LinkedServer.database.dbo.table

Produce this result?

  Error message: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1 Case expressions may only be nested to level 10.

Clearly there isn't a nested CASE expression here, though there are more than 10 "branches."

Another oddity. This inline table-valued function produces the same error:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
     @var varchar(20)
)
RETURNS TABLE
AS
RETURN
(
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table
)

But a similar multi-statement TVF works fine:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
    @var varchar(20)
)
RETURNS @result TABLE
(
    value varchar(max)
)
AS
BEGIN
    INSERT INTO @result
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table

RETURN;
END


texts: What does Can't find file: 'table_name' (errno: 2) mean?

texts: I have very little experience with databases, and today I encountered this error today and I have no clue what it means. I have done some research but I haven't been able to find a solution. The error started a day before when some results wouldn't show up. Let's say that the name was table1 then the table form today is table2. So, I was unable to display the information from table1 yesterday, and today I am unable to display content from table1 and table2. Table2 is the one that displays the Can't find file: 'table_name' (errno: 2) Any help or guidance would be highly appreciated.


texts: SQL Server Management Studio - Why is Dark Theme Disabled by Default?

texts:
Why is the Dark theme disabled by default?  Are there some bugs with this theme or something along those lines?  It seems odd to me that it is forcible disabled on purpose.

I found plenty of links that I made use of to turn on the 'Dark' Theme for SSMS however I'm curious to know why this setting is turned off by default.  Why is this one single line commented out in the ssms.pkgundef file?

Here's one of many links that show how to make available the disabled Dark Theme.
How to Enable Dark Them in SSMS

Basically I'm curious if there is some sort of consequence I'm unaware of by turning this feature back on.  I can't find any links that state why it's turned off in the first place.

  Screenshot of Dark theme enabled after making changes to ssms.pkgundef


texts: rebuild on clustered index, why does datasize shrink?

texts:
When we did a rebuild on a clustered index on a table that have about 15gb data in it and  the datasize shrunk to 5gb, how can this be? What kind of "data" is removed?

Data size i mean the "data" column of DBCC sp_spaceused

  Before Rebuild on clustered index:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    39169656 KB 15857960 KB 22916496 KB 395200 KB

  After Rebuild on clustered index:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    29076736 KB 5867048 KB  22880144 KB 329544 KB

TSQL for rebuild:

USE [DAX5TEST]
GO
ALTER INDEX [I_212RECID] ON [dbo].[LEDGERJOURNALTRANS] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE, FILLFACTOR = 85 )
GO


texts: Using SESSION sql_mode = 'NO_BACKSLASH_ESCAPES' before running query to remove backslashes in database

texts:
See this post:

How do I remove backslashes from records in MySQL

I have similar issues in that code that was developed before me did not use strip slashes with magic_quotes_gpc on before inserting it into a database. Now I want to remove a walk_array ('stripslashes'... call to gain some performance and prevent the stripslashes function from being run on every select instead of every insert or update (which happens much less).

Now my question is:

Is it not neccesary to use:

  SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES';

Before I run a query like this:

update tbl_closet set inlognaam=replace(replace(replace(replace(inlognaam,"\\\'","\'"),'\\\"','"'),'\\\\','\\'),'\\&','&');

Beacuse if I do not then the query will also use backslash as escape and things go wrong??


texts: SQL Server Transactional Replication from 2005 to 2017

texts:
As per the msdn, Subscriber version depends on the type of publication: A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.

I am having a requirement where I need to transactionally replicate a SQL Server 2005 to 2017, and as per the above msdn note we cannot do it, right?

But when I created a test environment and configured replication from 2005 to 2017 it is working fine. Any explanation to that? I am not able to understand why is it happening.


texts: How do I use MySQL Workbench to edit an existing, in-production database?

texts:
This is basically a repost of this question, which didn't give a satisfactory answer.

I'm finishing up the design of a database using an EER diagram. I now want to forward engineer and import the database into a working database. I will then use phpMyAdmin to start adding data and coding the site.

Now, while coding, what is the best way to make necessary changes to the database structure? Is the only way to export from phpmyadmin and import in MySQL Workbench? If so, how does it handle the existing rows when I add/remove columns? And, when it's time to forward engineer it again and import it via phpmyadmin, will all of my data still be there?

How do you handle this?


texts: SSIS Data Flow data access mode - what's the point of 'table or view' vs fast load?

texts: Using the SQL Server Business Intelligence Development Studio, I do a lot of flat file to OLE DB destination data flows to import data to my SQL Server tables.  Under "Data access mode" in in the OLE DB destination editor, it defaults to "table or view" rather than "table or view - fast load".  What is the difference; the only discernible difference I can perceive is that the fast load transfers the data much faster.


texts: Very large table makes the application performance too slow

texts:
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.

I found one table referenced in this app with 1,332,730,786 records in it.

Table IDSelection
IDType varchar(20) NOT NULL,
SelectID int NOT NULL,
UnID int NOT NULL,
Batch int NULL

Table has following indexes

IX_IDSelection_UnID nonclustered located on PRIMARY UnID
PK_UIDSelection clustered, unique, primary key located on PRIMARY SelectID, IDType, UnID

My concerns are
1. This table has composite primary keys with some entries in IDType that are empty string (not NULL though).
2. Running simple SELECT queries like below against this table takes quite some time.

select * from IDSelection where IDType <> '' and Batch is not NULL ORDER by Batch desc  -- cluster index scan
select * from IDSelection where Batch = 9977    -- 8 minutes run for 19 records
select * from IDSelection where IDType = 'ParentID' and Batch is not NULL  -- 9 min 3614603 rows

If I use indexed UnID in WHERE clause like the one below, performance is great

SELECT * FROM IDSelection where UnID = 1093510

Index fragmentation is not an issue here. Would it help adding identity primary key and making current composite key as [non clustered unique index] help? What other options do we have?

DB version is MS SQL 2014 Enterprise


texts: can delete manually a .ibd file and .frm file?

texts:
I have the following trouble in a mysql server database:

  190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.

  See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
  how you can resolve the problem.

Which is the secure way to resolve this problem?, fortunately is a database backup from the original. I have to run ALTER TABLE table_name DISCARD TABLESPACE or what should I do?

Regards.


texts: Frequent MySQL table corruptions and instability

texts:
The database I'm working on is having frequent issues.

Almost every day there is a problem.

Things like tables crashing or becoming corrupt.

So far, they've been repairable in one way or another, however I'm curious what the cause is exactly.

Bad hardware is always a possibility, but because I'm using Amazon's Web Services, I'm doubtful that is the case.

I've been monitoring the CPU usage on the machine and I've noticed that it frequently maxes out. On average, it hovers around 40%, but has frequent spikes.

Would constant high usage be a cause for my issues? If so, what is a good way of handling the high usage apart from optimizing queries; I'm currently working on that and the high usage is likely to stay.  Would replication be beneficial?

EDIT -- I went ahead and bit the bullet and decided to upgrade the resources. So far, I've been able to run all my queries without any issues. It looks like I was just working it too hard.


texts:
We're running MySQL on Azure. We have three very small databases.

The main database is POI and it hold 11 static records. During the day a few records may be added and deleted again within 1-2 hours.

We typically have 5-10 mobile users who poll the POI-DB for all 11-15 records every 60 seconds.

Several times every day, we do get some corrupt data from our SQL query. We often see two records which were deleted from the database two months ago.
What can make such a simple database become unreliable?

Server: eu-cdbr-azure-north-d.cloudapp.net via TCP/IP
Server type: MySQL
Server version: 5.5.45-log - MySQL Community Server (GPL)
Protocol version: 10
PHP extension: mysqli
PHP version: 5.4.42

--edit

I've changed our database to InnoDB. I still get the ghost-data.
The ghost data is not visible, when I use phpMyAdmin to view the POI-DB.

I started to measure the database connection time and realized that they sometimes are 1 - 5 seconds, so I's very likely to have concurrent requests, when the connection request is stocked.

Do I need to do some special tricks to handle concurrency?
The long connection times are probably caused by reverse DNS lookup. We will disable that now.


texts: One call to `decryptbypassphrase` for decrypting all columns

texts:
If I use ENCRYPTBYPASSPHRASE to encrypt the full table then how can I decrypt full table easily by using DECRYPTBYPASSPHRASE?

Actually I have to use DECRYPTBYPASSPHRASE ('passphrase', column_name) for every column to decrypt it. So it becomes so lengthy command. If I want to decrypt all columns then I have to specify this statement for all columns. Instead of that, can I use DECRYPTBYPASSPHRASE in queries so that I can use it only once and it decrypts all the columns?


texts: Filter on time difference between current and next row

texts:
I have a database table with the following structure:

id     | created
-------+---------------
1      | 2018-07-23 00:01:00
2      | 2018-07-23 00:02:00
...

Now I'd like to return all rows where the time difference to the next row is greater than e.g. 10 seconds.

I've tried with the Postgres window function, but couldn't get it to work, e.g.

SELECT
  created,
  created - lag(created, 1) OVER (ORDER BY created) delta
FROM tablename
where created - lag(created, 1)  OVER (ORDER BY created) > interval '10 seconds'
ORDER BY created;

The error I get is that I'm not allowed to use window functions in the WHERE clause. How to get this right?


texts:
I have a problem sorting strings in french :

This is my query :

SELECT LIBELLE as LibelleCourant,
       CODE as code
FROM S_MARQST
ORDER BY LibelleCourant;

And this is the result

LIBELLECOURANT  | CODE
----------------------
BAYARD          | BAY
LESIER          | LES
<Non renseigné> | INC
PAM             | PAM

As you can see, oracle seem to ignore the '<' charactrer and order only with alphabet characters. <Non renseigné> is neither at the top nor at the bottom. I tried NLSSORT(name, 'NLS_SORT=french'); but the result is the same.

I would like to have a basic ordering, with special chars either at the top or at the bottom (or both if it uses char codes, nevermind). As the data is user-generated, i cannot predict which chars will be used so i think i can not use CASE statements.

Thank you.

PS: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod


texts: Moving a large MariaDB database between MariaDB servers

texts:
Once my DB size is multiple hundred GB in size, it is not practical to use the documented methods for transferring a database to an alternate db server due to sheer amount of time (days to weeks) for that slow process.  The database is primarily located within a directory tree structure which can easily be copied, however, there seem to be files at the "data" directory (ibdata1, ...) not unique to a specific database, that contain some information that is required to successfully xfer to a new location.  Is there a way to extract the needed information from this file or set of files and provide to the new dB server?  Or is there some way to cause MariaDB to maintain each database within the appropriate directory tree, thereby permitting file transfers to function?
Some background on my objective:
I capture data to my databases on a specific server, but that server is limited to only 1TB disk size.
Once the space on that disk reaches a threshold level (say 10% remaining), I want to move that database to an alternate server with much greater storage available, and free the space on the capturing server.
Stating the objective a little differently: I wish to take a db from one server(1) and add that db to a different server(2), allowing all tables in the "copied" db(1) to be emptied, freeing space for additional collection. (but do so in finite time -- minuites/hours instead of days/weeks)


texts: Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE

texts:
I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested in a for each loop which parses an array of filenames / tables and runs through each combination until it is finished with all the files.

Here is my jdbc connection string where I am passing a definitive collation param/value for UTF8 collation

 static String  url = "jdbc:mysql://localhost:3306/stage?verifyServerCertificate=false&amp;characterEncoding=UTF8";

Here is my LOAD DATA string.

final String sql2 = ("LOAD DATA INFILE" + filetoEat  +
       "INTO TABLE staging." +tableName +
       "CHARACTER SET UTF8
       FIELDS TERMINATED BY','  ENCLOSED BY '\"\'
       LINES TERMINATED BY '\n'  IGNORE 1 LINES");

Create Table Statement

CREATE TABLE `zmi064_02` (
  `Material` varchar(11) CHARACTER SET utf8 DEFAULT NULL,
  `Material Description` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
  `MatType` text CHARACTER SET utf8,
  `MatGrp` text CHARACTER SET utf8,
  `ClassType` text CHARACTER SET utf8,
  `Class type Text` text CHARACTER SET utf8,
  `Class` text CHARACTER SET utf8,
  `Class Text` text CHARACTER SET utf8,
  `CharactName` text CHARACTER SET utf8,
  `CharactValue` text CHARACTER SET utf8,
  `Plant` varchar(6) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Show variables

character_set_client        utf8
character_set_connection    utf8
character_set_database      utf8
character_set_filesystem    binary
character_set_results       utf8
character_set_server        utf8
character_set_system        utf8
collation_connection        utf8_general_ci
collation_database          utf8_unicode_ci
collation_server            utf8_general_ci

The code is working fine until it comes across a special character like a degree symbol or micro symbol µ within a Material Description . At that point it throws an Exception

Invalid utf8 character string: 'LUG'

The string LUG is followed by a µ symbol. The DB is set to utf8 - utf8_unicode_ci and the column in question is a VARCHAR(60) that holds material descriptions. I have tried using  ESCAPED BY '\' but I can't seem to get it working correctly. I have also tried CHARACTER SET UTF8. I have also tried different collation ie, utf8_general_ci to no avail.

Any insight is greatly appreciated


texts: Can I safely kill OPTIMIZE TABLE on an InnoDB table?

texts:
MySQL's documentation for kill warns:

  Warning

  Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).

That's for MyISAM.

Is it also unsafe to kill an OPTIMIZE TABLE process running against an InnoDB table?


texts: SQL Server COUNT() seems to be ignoring WHERE

texts:
I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it is ignoring the WHERE clause for the grand total (but not the individual totals):

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(SiteTour) AS Expr1
FROM       tblConference AS tblConference_1

Here is what it is producing:

         0
Tour1   22
Tour2   30
Tour3   30
----------
TOTAL   83

(Tour1 count of 22 is correct, because there is one RegistrationType cancellation. However, the TOTAL of 83 should be 82.)

Also, I am getting an initial row, I guess based on counting NULLs, that shows a 0. Is there some way prevent this?

Please help me correct this. Thank you.


texts:
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.

@Catcall says this on the subject of storage order (clustered index) and the output order

  A lot of people believe that a clustered index guarantees a sort order on output. But that's not what it does; it guarantees a storage order on disk.
  See, for example, this blog post.

I've read the blog post by Hugo Kornelis and understands that an index doesn't guarantee that the sql server reads the records in a specific order. Yet I have a hard time accepting that I can't assume this for my scenario?

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

My original query was this:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

But I suggest that I could as well use this one (read below for my explanation):

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

As you can see, my table rows are small (16bytes) and I've got only one index, a clustered. In my scenario, the table consists of 100.000.000 records at this moment (and this will most likely increase tenfold).

When the database server queries this table it has two ways of finding my rows, either it seeks the primary key and thereby reading and returning my values in desc. order of Date, or it has to do a full table scan. My conclusion is that a full table scan on all those records will be way too slow and the database server will therefore always seek the table via its primary key and thereby returning the values sorted by Date DESC


texts: pgAdmin 4: How to establish a connection through an SSH tunnel/interface in Windows

texts:
I installed the latest pgAdmin 4 (v2.1) but I found out that (related thread), for the moment, the only way to establish a connection through an SSH tunneling is using another outside process, an example in the pgAdmin's forum says:

I setup a tunnel as follows:
ssh -f dpage(at)hostname(dot)enterprisedb(dot)com -L 5999: hostname.enterprisedb.com:5432 -N

(...) I was running on Mac, but the same procedure should work on Linux. On
Windows, there's a tunnelling app in Putty iirc, but I don't know how
to use it offhand.

I have found a simple PuTTY tutorial to establish a connection using an SSH Tunnel.
The connection is established but it seems, when I try to connect through pgAdmin it fails or is refused (using 127.0.0.1:3306 or 127.0.0.1:5432 as the tunnel's destination). Why?


texts: SQL Server 2008 : force user to change password at next login

texts:
How can I force users to change their password at next login? I want all my user change their password at next login to choose new password with new policy.

user must change password at next login is disable and just can be enable for new logins.


texts: Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?

texts:
Scenario

I have a database that contains all of our customer data, which is separated by
Schemas. For example:

Customer1 = Schema1,
Customer2 = Schema2,
Customer3 = Schema3,
etc...

Sometimes we get an issue where a Date value in a table is sometimes NULL. This table is contained in every Schema, but can sometimes be called 'Tasks', 'Tasks_NEW' or 'Task_NEW' (got to love consistency).

I am looking for a way that I can dynamically run a SELECT statement that will return a count of all NULL Date values, across all Tables and all Schemas.

So far, I have done this in order to put the Schema, plus the Table into a TempTable;

CREATE TABLE #TASK_TABLES
(   ID      INT IDENTITY(1,1),
    TenantID        INT,
    TableName       NVARCHAR(255)   )

INSERT INTO #TASK_TABLES
SELECT TABLE_SCHEMA, TABLE_NAME
FROM [Dynamic].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME IN ('Tasks', 'Task_NEW', 'Tasks_NEW')
ORDER BY TABLE_SCHEMA

Now that I have this information, I want to cycle through it with a SELECT like this;

SELECT Schema, COUNT(Date)
FROM [Dynamic].[Schema].[Table] (NOLOCK)
WHERE DATE IS NULL

Sadly, I don't fully know how to write Dynamic SQL as I'm still learning, but I was hoping that someone might be able to point me into the right direction?


texts: Truncating partition locking whole table in mysql

texts:
Novice here.
I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is as follows:
ALTER TABLE my_table DROP PARTITION p20210206;

The doc of mysql Here says when we drop a partition of innodb tables then the other partition does not get locked. However, when I perform any query during this process of partition deletion, they get locked.
I hit a small insert query and this is the status in FULL PROCESS LIST
    id: 32031012
   User: ab
   Host: myhost
     db: myDB
Command: Query
   Time: 1
  State: Waiting for table metadata lock
   Info: INSERT INTO my_table VALUES (9, 1, NULL, NULL, 'SUCCESS', '2020-02-05 09:46:13.356', '2020-02-05 09:47:36.869', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 20210307)

Can someone, please explain me why are the other partition getting locked and how can I overcome this problem.
Also, if the truncation query fails due to any reason, will the lock continue or will it be freed?


texts:
I'm doing a MySQL to Cassandra conversion. The fundamentals of the dataset are a master table of size ~10^7 rows to which several subsidiary tables are linked by foreign key. The subsidiary tables are up to size 10^10 rows.

The master table is really a set of seven columns, and it could be interpreted as a seven element partition key into which each 'row' in a subsidiary table could be assigned. These seven columns account for 99% of what is used with the WHERE clause for all SELECTS from the MySQL database. So far so good for conversion to the Cassandra schema.

Each of the seven columns in the master table has no more than 10,000 possible values, so each would seem to be a good partition key on its own. From reading about Cassandra database setup best practices, many people suggest a one table, one query approach. In this case, it would indicate that I should make seven tables, each using a single column of the master table as its primary key.

This would end up replicating many columns of data seven times over each. That this is the optimum strategy is something that my RDBMS wired brain can't seem to accept. For optimum read performance, should I replicate all columns of data into seven tables, one for each indexed column for searching?


texts: MS Access front-end cannot modify records when connected to MySQL

texts: I migrated my Access tables to MySQL hosted server and linked the tables back to MS Access front-end and now I cannot edit the records in my forms. I can see the data and go between records, but I cannot edit data at all. Cannot select other values from drop down, or even modify a misspelling. Can anyone help me with this issue?


texts: How to track failed logon attempts using Unified Auditing

texts:
I activated a ORA_LOGON_FAILURES policy and I've tried to execute

  select * from unified_audit_trail where unified_audit_policies = 'ORA_LOGON_FAILURES';

But it doesn't work. All my failed attempts to log in weren't audited. So what do I do wrong?


texts: Database Mirroring - Will forcing the service only lose non-committed data?

texts:
I have a database mirroring session in high safety mode between Server1 (Primary) and Server2 (Secondary)

According to this, synchronous data mirroring means

  every transaction committed on the principal database is also
  committed on the mirror server, guaranteeing protection of the data.
  This is achieved by waiting to commit a transaction on the principal
  database, until the principal server receives a message from the
  mirror server stating that it has hardened the transaction's log to
  disk. Note the wait for this message increases the latency of the
  transaction

With this in mind, if I lose Server1 completely (and irrecoverably) and force service onto the secondary using

ALTER DATABASE [MyDatabase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Would I only lose the transactions that were in-flight on Server1?


texts: Pulling data on SSL expiration from SQL server

texts:
I am trying to figure this out and seems to be out of thoughts and need your inputs to help me figure out on this.

We have implemented SSL across our 200+ servers, SQL2012 & 2014.

These cert are valid for 9 months . These are from vendor symantec.

Now the problem is tracking when they get expired. Keeping a manual track of these many servers are tedious.

Is there a way we can pull this up from SQL to get the information and create an SSRS report to check when they are going to expire?


texts: Combining data from two databases with same structure into one database

texts:
I have five SQL Server databases with the same schema in five different geographical locations. These locations send periodic backups to the central server which I restore in the five respective databases.

The requirement now is that data from these five databases MUST be combined into one database for consolidation.

Any suggestion for the solution is most welcome.


texts: Select data based on records created_at column

texts:
I have two tables Keywords, ProjectReports:

http://sqlfiddle.com/#!15/06ae3/1

As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know how.

In the position column I would like to display only the smallest value and just one value instead of that array. ex: [1, 2, 3]  will be 1
As you can see there is the keyword with id 4 which doesn't have any data in the project_reports table, in this case I would like to add the value 0 in the position column and add the current day date in the CREATED_AT column.
Also I would like to display the position value for the same keywords, where created_at column from project_reports table had the value now() - interval '1 day'; now() - interval '1 week'; now() - interval '1 mon', if there is no position for those days add 0.

Expected output:

Keywords:

 ID: 1 name: test
 ID: 2 name: test1
 ID: 3 name: test2

Project_reports:

 ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
 ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'
 ID: 3, keyword_id: 1, project_id: 1, position: 3, created_at: '2014-07-10'
 ID: 5, keyword_id: 1, project_id: 1, position: 4, created_at: '2014-07-10'
 ID: 6, keyword_id: 2, project_id: 1, position: 1, created_at: '2014-07-09'

After query

k_name: test, k_id: 1, p_id: 1, position 3, yesterday_position: 1, created_at: '2014-07-10'
k_name: test1, k_id: 2, p_id: 1, position 0, yesterday_position: 1, created_at: '2014-07-10'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-10'
k_name: test, k_id: 1, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test1, k_id: 2, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-09'


texts: Access denied for user 'root'@'localhost' What proces is causing this?

texts:
I got this connections constantly in logs, how to trace what app is trying to connect?

root@localhost is allowed to connect and working for some apps. I think there is some app without changed password. How to trace it?

  190310 22:38:57 [Warning] Access denied for user 'root'@'localhost'
  (using password: YES) 190310 22:38:59 [Warning] Access denied for user
  'root'@'localhost' (using password: YES) 190310 22:38:59 [Warning]
  Access denied for user 'root'@'localhost' (using password: YES) 190310
  22:39:03 [Warning] Access denied for user 'root'@'localhost' (using
  password: YES)


texts:
I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk.

My SELECT is of the form:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
        LEFT JOIN t3 ON t1.id=t3.id
        ...

I know by how these tables were populated that this result corresponds exactly to simple column binding, so that ON t1.id=t2.id and ON t1.id=t3.id can be omitted. The problem is, as far as I know, RDBMSs can operate only projections and row binding. Column binding isn't a typical operation.

So the question is the following: is it possible to bind columns "as they are" without specifying any join criteria? For example, the following query, which is syntactically wrong, should explain what I mean:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3 ...

I'm using PostgreSQL 9.4.5.

Data Example

Input tables:

t1.id | t1.value
----------------
    1 |       a
    2 |       a
    3 |       b
    4 |       a
    5 |       c
    6 |       a

t2.id | t2.value
----------------
    1 |       g
    2 |       g
    3 |       h
    4 |       g
    5 |       o
    6 |       l

t3.id | t3.value
----------------
    1 |       e
    2 |       e
    3 |       e
    4 |       e
    5 |       q
    6 |       e

Expected result:

t1.id | t1.value | t2.value | t3.value
--------------------------------------
    1 |       a  |        g |       e
    2 |       a  |        g |       e
    3 |       b  |        h |       e
    4 |       a  |        g |       e
    5 |       c  |        o |       q
    6 |       a  |        l |       e


texts: How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

texts: I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.


texts:
I am busy rebuilding an entire MySQL DB. There are multiple schemas and multiple tables in each schema.

I am restoring them using *.frm files as all other methods used have either failed or corrupted the data.

I am using the command:

mysqlfrm --server=root:password@localhost:3306 F:\DirectoryStructure\File.frm > file.txt --diagnostic

Is there a way to export ALL frm files from the same command using only one command? As doing each file one by one is going to take forever. I have tried using:

mysqlfrm --server=root:password@localhost:3306 F:\DirectoryStructure\*.frm > filename.txt --diagnostic

but this doesn't work.

Any help is really appreciated


texts: Does creating an index on BIT(n) attribute in MySQL make sense?

texts:
I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.

I've seen some mentions on the internet that BIT attributes are not index-friendly in MySQL. Without further elaboration though.

So the question is - do MySQL indexes work well with BIT(n) attributes or I better find some other ways, like restrict n to, say, 32 and use INT instead?


texts: How do I check the connection timeout in Oracle?

texts:
I need to check Oracle 12c timeout for an active connection. I don't have access to the database itself but I do have some creds. Is there a table to query to get this information?

Thank you!


texts: I have this database table where I insert multiple results during 1 action. In php I have a submit button when pressed, inserts 1-50 rows(depending on the user input). I've got a foreign key in this table which has the same value on the rows inserted during that action(so when the primary key connected to the foreign key gets deleted, the 1-50 rows will be deleted as well). Now I've got an Auto Increment in my table and during testing, I find out that the ID's are somewhat like this: 1,2,3,200,249,460,670. So like huge differences between the ID's. Whenever I would create a new row, it will get the ID 671. I feel like it would be better if the ID would just get 4 in this case. Is there any way of doing this automatically and not typing ALTER TABLE users AUTO_INCREMENT=4; manually? If so, are there any advantages/disadvantages?


texts:
I am learning sql and I am doing another tricky query. I have the following tables.

players: Columns: name, school_name, gender

simple: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

doubles: Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple and double tables will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

I have the following query:

SELECT t1.player_name_fkey,
       t1.school_name_fkey,
       score_simple,
       score_double,
       (t2.score_simple + t1.score_double) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         sum(score) AS score_double
  FROM double
    LEFT JOIN players ON players.name = double.player_name_fkey
  WHERE category = 'Cadet'
    AND gender = 'Féminin'
  GROUP BY player_name_fkey,
           school_name_fkey
) t1
  LEFT JOIN (
    SELECT player_name_fkey,
           school_name_fkey,
           sum(score) AS score_simple
    FROM simple
      LEFT JOIN players ON players.name = simple.player_name_fkey
    WHERE category = 'Cadet'
      AND gender = 'Féminin'
    GROUP BY player_name_fkey,
             school_name_fkey
  ) t2
         ON t1.player_name_fkey = t2.player_name_fkey
        AND t1.school_name_fkey = t2.school_name_fkey
ORDER BY sum_scores DESC

The problem that arises is when one of the tables do not have a matching score for a person, it returns an empty string for that score. I wish that in this case, the score gets set to zero. So I tried this:

SELECT t1.player_name_fkey,
       t1.school_name_fkey,
       score_simple,
       score_double,
       (t2.score_simple + t1.score_double) AS sum_scores
FROM (IF NOT EXISTS (
  SELECT player_name_fkey,
         school_name_fkey,
         sum(score) AS score_double
  FROM double
    LEFT JOIN players ON players.name = double.player_name_fkey
  WHERE category = 'Cadet'
    AND gender = 'Féminin'
  GROUP BY player_name_fkey,
           school_name_fkey) t1
) score_double = 0
  LEFT JOIN (
    SELECT player_name_fkey,
           school_name_fkey,
           sum(score) AS score_simple
    FROM simple
      LEFT JOIN players ON players.name = simple.player_name_fkey
    WHERE category = 'Cadet'
      AND gender = 'Féminin'
    GROUP BY player_name_fkey,
             school_name_fkey
  ) t2
         ON t1.player_name_fkey = t2.player_name_fkey
        AND t1.school_name_fkey = t2.school_name_fkey
ORDER BY sum_scores DESC

Unfortunately, I get the error:

  "ERROR:  syntax error at or near "NOT".


texts: No syntax for Nonclustered index in MySQL?

texts:
So, I have seen people discussing about differences between clustered V Non Clustered index, however, I didn't see any syntax for Non Clustered Index in MysQL. How does MySQL creates Nonclustered Index?

The only thing I saw is Secondary Index in the documentation here


texts:
I have the following problem:

After I login to my mysql database server using "mysql -u root -p" on my Ubuntu 14.04 Server everything seems to be OK, as I can login without a problem.
But then the problems start, as I would like to show using the examples below:

mysql> show grants for 'root'@'%';
+------------------------------------------------------------------+
| Grants for root@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `cloud`.* TO 'root'@'%'                  |
| GRANT ALL PRIVILEGES ON `dentalfraeser24`.* TO 'root'@'%'        |
| GRANT ALL PRIVILEGES ON `joomla`.* TO 'root'@'%'                 |
+------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show grants for 'root'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

Connection id:      626
Current database:
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.44-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         16 min 38 sec

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

mysql> create database test;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'test'

It looks like the system has some kind of split personality and sometimes thinks it is 'root'@'%' and other times 'root'@'localhost'.

The thing is, I cannot install anything mysql related anymore as no new database tables can be created.
I must say that there was once Plesk installed on the server, which I removed, as I found it horrible.

Does anyone have a hint for a solution?


texts: MongoDB - count current read/write operations

texts:
is it possible to count the current read or write operations on a mongodb replica set? (including all databases not just a single database)
Thank you!
Best regards


texts: Write-lock a whole table during transaction

texts:
I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be in an inconsistent state and any concurrent MySQL session shall not be allowed to modify it until commit-ed.

The use of SELECT ... FOR UPDATE | LOCK IN SHARE MODE is not suitable because, while it may potentially be used to lock all the rows in the table, it won't prevent the insertion of further rows by a concurrent session. Basically, I need to LOCK TABLES my_table WRITE within the body of a transaction.

The table contains about 20,000 rows and a master-slave, mixed-format replication is in place over a slow connection so, for any workaround, I'd prefer to avoid using temporary tables which may faint the slave, and the amount of data dumped into the binlog should ideally be minimized.

The engine is InnoDB on MySQL 5.6, for both master and slave.


texts: Unable to alter table - reason code is 10

texts:
I'm trying to run this:
ALTER TABLE AMMLIBT.SAGRPPF ADD UNIQUE (ID) ADD CHECK ( IS_BUDGETABLE IN ( 0 , 1 )) ;

Which gives me this:

SQL State: 55019
Vendor Code: -7008
Message: [SQL7008] SAGRPPF in AMMLIBT not valid for operation. Cause . . . . . :   The reason code is 10.  Reason codes are:
[...]
10 -- A constraint or trigger is being added to an invalid type of table, or the maximum number of triggers has been reached, or all nodes of the distributed table are not at the same release level.
[...]
Recovery  . . . :   Do one of the following based on the reason code:
[...] 10 -- Specify tables that are valid for constraints or triggers.
[...]

...And I have no idea why and can't seem to find anything online explaining this.
The table is journalled and I've deleted all dependents.
I'm on IBM DB2 V7R1
How do I fix this?


texts: retrieving all related rows via foreign key before deletion

texts:
I have a table A, Many other tables have foreign keys which cascade on delete. Is there anyway to get all the deleted rows before, using

  delete from A where id = 1

. Basically What I want is to know which rows from other tables are getting deleted.

I don't want to hard code to other tables, as we might add more tables later on, and I do not want to keep updating the query every time a new table is added.

We might even have more connection like table B have foreign key in A, and table C might have a foreign key in table B


texts: When do you start troubleshooting a SQL Server 2005/2008 deadlocks and how? The alert is turned on SSMS via SQL Server performance condition alert, objects->SQLServer:Locks, Counter->Lock Waits/Sec, Instance:_Total, alert if counter: rises above value 3.  Is this a proactive way of monitoring it? What is the acceptable value? I would highly apprecate your help. Thank you!!!


texts: Environmental Variables for OracleDB 12 in Oracle Linux are Overriding Each Other with SQLPlus Variables

texts:
I want to set the environmental variables for OracleDB and SqlPlus properly. However, they are overriding each other. I'm doing something wrong, and I would like to get a hand finding where I went wrong.

Setup:
Oracle Linux 7
OracleDb 12.1.0.2.0 (12c)
Sqlplus 12.2.0.1.0

Here is my scenario.
Step 1: First, I set the environmental variables for OracleDB in "One" terminal window, as follows
[myuser]$ export ORACLE_HOME=/u02/app/oracle/product/12.1.0.1/dbhome_1
[myuser]$ export ORACLE_SID=o12c
[myuser]$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
[myuser]$ export PATH=$ORACLE_HOME/bin:$PATH

Step 2: Second, I set the Environmental Variables for SQLPLUS in the "One" terminal window used in step 1, as follows:
[myuser]$ ORACLE_HOME=/usr/lib/oracle/12.2/client64
[myuser]$ PATH=$ORACLE_HOME/bin:$PATH
[myuser]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
[myuser]$ export ORACLE_HOME
[myuser]$ export LD_LIBRARY_PATH
[myuser]$ export PATH

Then, I perform the following command.
[myuser]$ sqplus / as sqldba

Which comes back with the following error.
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 18:57:44 2017

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:

If I enter any user name (currently logged on user included), I get the following error:
Enter user-name: sysdba
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Lastly, if I try to execute the "nolog" command, I get the following.
[oracle@localhost Desktop]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 19:01:10 2017

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

And, I tried to enter the conn / as sysdba command, and I get the following:
SQL> conn / as sysdba
ERROR:
ORA-12545: Connect failed because target host or object does not exist

If I run the command, "env" I get the last Oracle_home variable set. In the scenario described above. It's SQLPlus Oracle_Home.


texts: Problem with a partitioning switch script

texts:
Working on some dynamic SQL to create a 13 month rolling partitioned table.  See here for some background info.

The dynamic SQL part is simple enough, and the first partition switches out without a problem.  Unfortunately when I get to the second partition I start getting this error:

Msg 4972, Level 16, State 1, Line 16
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'DBName.dbo.TableName_Partition2' allows values that are not allowed by check constraints or partition function on target table 'DBName.dbo.TableName'.

I'm sure it's something simple but I can't quite figure it out.  Here is the code.

-- Existing structure
USE [SourceDB]
GO

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
);

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO

USE [DestinationDB]
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS
 RANGE RIGHT FOR VALUES
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme]
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

SELECT *
    INTO [TableName_Partition2]
FROM SourceDB.dbo.[SourceTable]
WHERE Modified >= N'2015.08.01'
  AND Modified < N'2015.09.01';
GO

CREATE CLUSTERED INDEX [TableName_Modified]
    ON [dbo].[TableName_Partition2] ([Modified] ASC);
GO

ALTER TABLE [dbo].[TableName_Partition2]
    ADD CONSTRAINT [CK_TableName_Partition2]
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2]
    SWITCH TO [DestinationTable] PARTITION 2;
GO

DROP TABLE TableName_Partition2;
GO

I've tried each partition number, I've tried reducing the time range to a single date (ie modified = '2015.09.02') but still get the same error.  I'm stumped.

Thanks for any help.


texts: Get Master Server Information from Slave instance

texts:
We need to get Master server information such as server IP and Port, we know this information is dumped into master.info file by default but what if it required within MySQL prompt as we are using windows enviorment and can not use system command too to read file from bash being in MySQL prompt and couldn't restart instance as well.

Is there any way of getting Master Server info from Slave server using some sort of dynamic select statements within MySQL prompt.

MySQL Version is 5.6.26 community  and  OS is Windows 2008R2

Any help is much appreciated.


texts: Improving query to generate a million empty rows

texts:
I have been asked to review a process that sadly is highly inefficient but we are held in the short term to the design.

We currently generate based on a range a set of rows inside a table to hold future receipts. The ReceiptData table for every Receipt id has its rows generated first and as the receipts are use the next is populated.  Again this process for the short term is in stone.

When a customer adds a receipt we then need to generate a million rows within the receiptData table that will then be used in the future.

DECLARE @tempT TABLE
(
    number INT
)
DECLARE @recId AS INT -- represents an id a parent table id
DECLARE @receiptStart AS INT --represents where in the sequence to begin normmaly 1 but could be 1500 etc.
DECLARE @receiptEnd AS INT -- represents then end in relation to the start normally 999999 but could be 456789, defined by customer

SET @recId = 12
SET @receiptStart = 120
SET @receiptEnd = 999999

    DECLARE @iCounter AS INT = @receiptStart

WHILE ( @iCounter <= @receiptEnd)
  BEGIN
      INSERT INTO @tempT
      VALUES      (@iCounter)
      SET @iCounter += 1
  END

INSERT receiptdata
       (recid,
        receiptnum,
        negativereceipt)
SELECT @recID,
       Cast(number AS VARCHAR),
       0
FROM   @tempT
WHERE  NOT EXISTS ( SELECT  1
                    FROM    receiptdata
                    WHERE   ffid = @recId
                            AND receiptnum = number
                  )

I ran this and it took over 13 minutes to process...there has to be a better way.  The initial population of @tempT takes over 30 seconds; the insert into ReceiptData takes the majority of the time.

The annoying piece of this is the receiptnum within the receiptData table is a varchar.  while we plan out a future design we are stuck with all of this in the short term.

Any suggestions? it it rather straight forward but insanely slow.
we usually go with ranged from 1 to 999999 for receiptStart and receiptend.


texts: Read Concern Majority when majority nodes are down

texts:
I have a 6 node replica set (all of them data bearing) with 5 voting members. If I lose 3 voting members, I will not have a majority voting members and my replica set will become read-only.
In this scenario, if the application has a read concern of "majority", will the reads fail? I have read that this majority read concern introduces a "cache pressure" but will the reads fail?? Or do I need to disable the RC by the below options for basic reads (with majority) to work?

--enableMajorityReadConcern command line option to false.

replication.enableMajorityReadConcern configuration file setting to
false.


texts: Drupal database doesn't work after MySQL restart

texts:
I've installed Drupal 7x on my local XAMPP 1.8.3 Server. Everything went fine but after adding some modules, changing the drupal theme and restarting the MySQL Server, the MySQL Server is dropping some errors and stopped working.

*mysql_error.log*

  2013-09-02 16:18:46 2544 [Note] Plugin 'FEDERATED' is disabled.

  2013-09-02 16:18:46 3e8 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

  2013-09-02 16:18:46 2544 [Note] InnoDB: The InnoDB memory heap is disabled

  2013-09-02 16:18:46 2544 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

  2013-09-02 16:18:46 2544 [Note] InnoDB: Compressed tables use zlib 1.2.3

  2013-09-02 16:18:46 2544 [Note] InnoDB: Not using CPU crc32 instructions

  2013-09-02 16:18:46 2544 [Note] InnoDB: Initializing buffer pool, size = 16.0M

  2013-09-02 16:18:46 2544 [Note] InnoDB: Completed initialization of buffer pool

  2013-09-02 16:18:46 2544 [Note] InnoDB: Highest supported file format is Barracuda.

  2013-09-02 16:18:47 2544 [Note] InnoDB: The log sequence numbers 1600614 and 1600614 in ibdata files do not match the log sequence number 1600644 in the ib_logfiles!

  2013-09-02 16:18:47 2544 [Note] InnoDB: Database was not shutdown normally!

  2013-09-02 16:18:47 2544 [Note] InnoDB: Starting crash recovery.

  2013-09-02 16:18:47 2544 [Note] InnoDB: Reading tablespace information from the .ibd files...

  2013-09-02 16:18:47 2544 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace drupal/variable uses space ID: 2 at filepath: .\drupal\variable.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd

  InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd

  InnoDB: We do not continue the crash recovery, because the table may become

  InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

  InnoDB: To fix the problem and start mysqld:

  InnoDB: 1) If there is a permission problem in the file and mysqld cannot

  InnoDB: open the file, you should modify the permissions.

  InnoDB: 2) If the table is not needed, or you can restore it from a backup,

  InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

  InnoDB: crash recovery and ignore that table.

  InnoDB: 3) If the file system or the disk is broken, and you cannot remove

  InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

  InnoDB: and force InnoDB to continue crash recovery here.

I tried to fix the problem with the solutions named above, but it didn't work. I've turned the UAC off (for point 1.: permission problem). Deleted the .idb File for point 2 and to point 3: I've looked everywhere for the my.cnf but couldn't find any. I don't know where to look for the problem/solution :( .


texts: Modify all tables in a database with a single command

texts:
Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;

My objective is to modify the charset from latin1 to utf8 to all tables.

UPDATE: RDBMS is MySQL


texts: Disk Usage Report SSMS - query to obtain same info

texts:
I would like to have a query that obtains the same info as in the Disk Usage report, section data/Log files autogrowth/autoshrink events

I would like to build a custom monitoring report but i could not find where this report comes. the only thing I could find is that this info comes from:

sys.fn_trace_gettable

but i don't know what field or joins I must do to have the same functionality as the report above.

EDIT: i need these fields, also the size of the growth (for analytical purposes)

thanks for your help.


texts: GIS Point or discrete longitude and latitude columns?

texts:
I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 bytes, so is it just an abstraction for two 8 byte floats? Or is it a string containing some kind of geohash that optimizes the performance of certain queries?

Let's say I want to get all the points for a specific viewport on a Leaflet map... with numerical columns representing latitude and longitude, I'd just filter for all the rows that have a latitude number between the N/S bounds and a longitude number between the E/W bounds, easy. I looked into using Point here, but the syntax is cumbersome and it seems to me like unnecessary overhead for development and for database performance... is it?


texts: Joining same table vs Joining different table in Mysql?

texts: Is there any difference between joining same table again and again and joining different tables? Internally it is just another join. But will it make a difference? Like If I join same table again and again only index of that table alone will reside in memory. So is there any other things like this? Or Will joining same table again and again degrade performance?