texts: How to sort the id users that have the maximum of a property?

texts:
How to return the id of users that have the maximum of a property in a database?

id A B C

with A B and C being integer?

For instance: if managing a database of users that stole things in a computer game. I want to return users that have stolen the maximum amount of a resource called Metall.

The database sketch is the following:

id  Name    Adresse     Verteidiger     Metall  Kristal     Deuterium   Zerstörung  Datum

Even if this is false, I tried:

SELECT id I1 FROM `Angriff` WHERE NOT EXISTS
    SELECT id I2 WHERE Metall > I1.METALL;


texts: How to generate error log file while executing a .sql file in terminal

texts:
I just started learning coding 2 week ago and I am in a middle of an issue that I am not really able to find online, SO i though why not ask.

I am trying to import Import a MySql DB of around 8 Gigs through Terminal (SSH) But the file execution always stop in the middle somewhere.

My Question is:
Is there a way to write the error log generated my mysql while running the query in terminal backend?
The 2 queries I am using are:

1/mysql -uroot -pxxx -f main < sq2.sql &

2/ gzip -dc < file.sql.gz | mysql -f -uroot -pxxx web &

Both queries run as a back-end daemon because of "&" and "-f" helps in forcing the mysql to run even when an error is encountered.

But still my query gets terminated in the middle, So how can I write and log errors for such commands.


texts: Sorting by DATETIME slower than by TIMESTAMP?

texts:
I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ?

Because I noticed that ordering by numbered keys is done almost instantly.


texts: How to find values with multiple consecutive upper case characters

texts:
For a project that I am working on, I need to identify values that have incorrect uppercase.

For example, I need it to identify the following types of values:

Mr JOHN Smith
MR John Smith
Mr John SMITH
Mr JOhn Smith

Ideas I have tried so far:

Select * from table where Name = upper(Name)
  collate SQL_Latin1_General_CP1_CS_AS

(which gives results for rows that are all upper case, such as MR JOHN SMITH)

and

Select * from table where right(Name,3) = upper(right(Name,3))
  collate SQL_Latin1_General_CP1_CS_AS

which picks up some rows too.

But I doubt this is the most efficient way to go about this.


texts: Hierarchical data: get all nodes from a hierarchy, including parents that don't match the where

texts:
I have a table similar to this structure:

TaskId:  int PK
AssignedTo: varchar(50)
ParentTaskId: int FK nullable

ParentTaskId references TaskId.

The data in the table looks similar to this:

TaskId     |     AssignedTo     |    ParentTaskId

1                Jim                 NULL
2                Jim                 1
3                Joe                 2
4                John                NULL
...

The crux of my issue is this:  I need to query the table for all tasks assigned to Joe (which would return TaskId 3), but I also need to show the hierarchy for the found task, so I need to return Tasks 1, 2, and 3 from the above table.

I'm lost as to how do pull this data from SQL, either with one query or multiple queries.

Help please!  Using SQL Server 2012.


texts: MySQL Replication - Mixed Mode Error 1032

texts:
Im using MySQL replication with binlog_format=mixed on both master and slave, but still Im getting error 1032.

[ERROR] Slave SQL: Could not execute Update_rows event on table mydb.mytable;
 Can't find record in 'tablename', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000134, end_log_pos 739573, Error_code: 1032


texts: SQL Server How to Set up Maintenance plan to shrink log

texts:
So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL".

When it is to FULL and I perfrom shrink -> log -> it doesnt reduce much
but If I set recovery model to simple it reduces it to 1 MB.

The problem is as I see it I need to leave it at full so my back ups have full version of the database.

Now I created a maintenance plan to shrink database, but im noticing it also almost doing no shrinking again because I guess recovery model is at full. How can I get a maintenance plan working which will actually reduce the log file ?


texts: How to view a list of databases under server in SQL Anywhere like we do in SQL Server

texts:
I am very new to SQL Anywhere. I have been working with SQL Server for a long time.

In SQL Server, if we need to see the list of database under server, we can see that under Server Explorer.

How can I do same in SQL Anywhere?

I have just installed SQL Anywhere 16 and have no idea how to find Server name etc.


texts: sql taking < 1 sec to run in sql developer, 37 on the application side

texts:
As the title says, we have a big discrepency between a sql query we're running from our java application (jdbc thin client) and the same query run in sql developer.

I clear the cache of the query before running it locally as follows:

exec DBMS_SHARED_POOL.PURGE ('000000029F12AE38, 3000986076', 'C');

The address and hash values are retrieved from the v$sqlarea view. I always get the freshest data to proceed. (If you think it's not enough to flush the cache, please kindly let me know)

Query:

select
  t.zoo_equipment_type || ': ' || t.zoo_equipment_type_descr equipment,
  t.zoo_service_descr_1 service_descr,
  tr.resourceid,
  trunc(t.startdate) start_date,
  trunc(t.finishdate) finish_date
from eai_taskrsrc tr
inner join eai_task t on tr.snapshot = t.snapshot and tr.activityobjectid = t.task_id
inner join eai_project p on t.snapshot = p.snapshot and t.proj_id = p.proj_id and p.eai_snapshot_type = 'P'
inner join (
  select
    eai_project.eai_snapshot_project,
    'P',
    max(eai_project.eai_snapshot_period) max_period
  from
    eai_project
    join eai_res_spread on eai_res_spread.snapshot = eai_project.snapshot
    join eai_task on eai_task.snapshot = eai_res_spread.snapshot and eai_task.task_id = eai_res_spread.task_id
  where
     eai_snapshot_project IN (:1 )
    and eai_snapshot_type = 'P'
  group by
    eai_project.eai_snapshot_project,
    'P'
) max_period on max_period.max_period = p.eai_snapshot_period
  and max_period.eai_snapshot_project = p.eai_snapshot_project
where
   p.eai_snapshot_project IN (:2 )
  and  t.zoo_unit IN (:3 )
  and  1 = 1
  and  1 = 1
  and  1 = 1
  and  1 = 1
  and  1 = 1
  and  t.zoo_equipment_type IN (:4 ,:5 )
  and  tr.resourceid IN (:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 ,:24 ,:25 ,:26 ,:27 ,:28 ,:29 )
group by
  t.zoo_equipment_type,
  t.zoo_equipment_type_descr,
  t.zoo_service_descr_1,
  tr.resourceid,
  trunc(t.startdate),
  trunc(t.finishdate)
order by
  t.zoo_equipment_type,
  t.zoo_service_descr_1,
  tr.resourceid
;

Explain plan from SQLDEVELOPER:

Explain plan from Oracle Enterprise Manager:

They look alike, although not exactly the same. I don't know why, since both queries are equivalent (I took the one in EM and replaced the placeholders with the real values from the application).
But what really troubles me is that the time spent, according to enterprise manager, is almost 100% CPU, 37 out of the 37 seconds.

Hard drive activity flat, and total buffer reads 475k.

Do you have an idea why this is happening? I restarted my whole VM, application server, database... just to make sure, but it didn't change anything.

Thanks


texts: querying a linked PostgreSQL database in SQL Server 2008

texts:
We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server.  I am exploring options for real-time or near real-time reporting in this environment.

I am curious about linking the Postgres database to our SQL Server environment.  Would I be able to build views against the Postgres database that our SQL Server report writers could then use?  Are there any additional performance considerations beyond the normal ones?  (i.e. views are slower than tables)


texts: Table values being cut-off by table lines

texts: Is there a way in the SSRS table component to avoid that the values get cut-off by the lines of the table.


texts: SQL Server 2012 Replication - MSrepl_commands growing all set to in progress

texts:
I'm stuck looking at this problem that we've had since our last restart almost a week ago.

The distribution database is growing and no longer pruning; it appears that everything in the MSdistribution_history are all set to either 3 or 4 (in progress / idle) all the idle report as no replicated transactions are available)

We're now at 261K values in the history table and 195M rows in the repl_commands table

All replication through this distributor goes from one location to up to three different servers, there is currently a maximum of 4 seconds latency across all replication, cant see anything that isn't complete

Obviously running the sp_MSmaximum_cleanup_seqno command returns nothing as there is nothing that is set to be success (status 2)

Does anyone know what might have gone wrong or any possible solutions.

General things:

All replication is transactional without any snapshots
immediate_sync is turned off on all replication
max retention is set to 72 hours
no errors are being reported
there were a few issues when the server came back up where all the jobs retried twice.
The publisher server was patched from 2012_SP2_CU7 to SP3_CU2 and the distributor instance has not yet been patched

We're going to try a fail over to the secondary node tomorrow to see if that will clear it out

I can always manually delete stuff from the repl_commands but I'm highly hesitant to do this.

Any advice or ideas?


texts: How do I automatically deliver the SQL Server built in reports?

texts:
When you right click on a number of items in the object explorer, it gives you the option for a reports menu. Various reports may exist depending on which type of item is selected. Yet, there does not appear to be any built-in functionality for automated delivery of these reports. It is hidden away somewhere?

My real question : Is there any way to set some of these up to be automatically delivered through email? The reports I am targeting are the "Job Steps Execution History", and "Top Jobs" reports for SQL Server agent. I am using SQL Server 2005/2008


texts: Unusual behaviour of a query when using case expression in group by, some records are being missed

texts:
I have 4 table with below structure:
Table "Customer_Info"
 create table Customer_Info
(
  Customer_Number         int,
  Customer_Issued_Branch  int,
  <Other Columns related to customer>
)

Example Data:
  Insert into Customer_Info(Customer_Number,Customer_Issued_Branch)
  values (1,1001),(2,1002),(3,1003),(4,1001)

Table "Customer_Transaction_Info"
create table Customer_Transaction_Info
(
  Customer_Number    int,
  Channel_Number     int,
  Transaction_Count  int,
  <Other columns related to customer transactions >
)

Example data:
Insert into Customer_Transaction_Info(Customer_Number,Channel_Number,Transaction_Count)
Values(1,5,2),(1,6,1),(1,3,1),(1,1,1),(1,2,1),(2,5,1),(2,1,1)

Table "Customer_Channel_Info"
create table Customer_Channel_Info
(
  Customer_Number         int,
  Channel_Number          int,
  Channel_Branch_code     int
)

Example data:
insert into Customer_Channel_Info (Customer_Number,Channel_Number,Channel_Branch_code)
values (1,5,1001),(1,6,1002),(1,3,1003),(2,5,1004),(3,6,1005),(4,3,1006)

Table "Branch_Info"
create table Branch_Info
(
  Branch_Code      int,
  New_Branch-Code  int
)

Example Data :
insert into Branch_Info(Branch_Code,New_Branch-Code)
values(1001,1010),(1002,1010),(1003,1010),(1004,1010),(1005,1010),(1006,1010)

The problem is that everything seems fine until this part of my query on t4.Branch_Code = t2.Channel_Branch_code. what I mean is that the select * of below query, without group by and sum(), gives me 7 records, but as soon as I add group by part and sum, the below query returns 4 records! It's wired and I do not understand the reason! both case statements,in group part and select part of the query are exactly the same so why some record are being missed?
My Query is :
select t1.Customer_Number,
       case
         when t1.Channel_Number in (1, 2) then t3.Customer_Issued_Branch
         else t4.New_Branch-Code
       end brncode,
       sum(case
             when t1.Channel_Number = 1 then t1.Transaction_Count
             else 0
           end) Tel_Count
  from Customer_Transaction_Info t1
  left join Customer_Channel_Info t2
    on t1.Customer_Number= t2.Customer_Number
   and t1.Channel_Number = t2.Channel_Number
  left join Customer_Info t3
    on t3.Customer_Number = t1.Customer_Number
  left join Branch_Info t4
    on t4.Branch_Code = t2.Channel_Branch_code
 group by t1.customer_num,------------------------>/*This part*/
          case
            when t1.Channel_Number in (1, 2) then
             t3.cust_branch_cod
            else
             t4.New_Branch
          end

Thanks in advance


texts: SQL string field lengths in a db having about 6 different types of addresses, with about 20 markets and environments

texts:
(Someone suggested I should ask this here too; previously posted on https://softwareengineering.stackexchange.com/questions/231335/sql-string-field-lengths-in-a-db-having-about-6-different-types-of-addresses-wi )

I have been advocating lately that all string fields should be ntext / nvarchar(max) - we're using MS SQL Server. The objections seem to be either "it's not a good idea" (without any reason) or "that would allow the customer to send a lot of crap". Since I believe in filtering out the crap before it hits the database, is there a good reason why my idea is bad?

My main argument for not limiting the string fields is that I have discovered that we have about six different types of addresses (customer, reseller and so on), with about 20 markets (each having its own database) and environments. I have spotted at least ten different lengths for say Address1 and the same thing happens for other fields. Instead of "standardizing" them and then discovering that we missed something, I suggested that we go ahead and limit the inputs and let the database handle anything.

Does anyone have a pro/con I don't see?


texts: Single select from template record oracle

texts:
Due to lack of terminology in my head, let's say I've got 1 record from users table with these attributes :

Name   | Surname | Height | Weight
Person   X         6,2      190

How do I find other users with same height and weight as a person x with single select?

Update:

I have this on me now :

Select *
from users
where height = (select height from users where name = 'x')
and weight = (select weight from user where name = 'x');

Can I somehow fire this inner query once, because I'm reusing the same db record?


texts: Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?

texts:
We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always have to worry about filtering out the "marked as deleted" rows, which seems like an unnecessary headache.

However, we are auditing deleted rows in separate audit tables so as to keep the deleted data separately.  We have a generic audit trigger that inserts all deleted columns (and their values) to the audit table on delete.  We wish, though, to also audit the user ID of who deleted the row anytime a row is deleted. My idea is to have a deleted_by_user_id column on each table, and a trigger that blocks a delete if this column is not set. The transaction should fail and rollback if the "deleted by" user is not specified.

It would also automatically delete the row when it was set during an INSERT or UPDATE, meaning the row could only be deleted by setting this column.  This would mean that any delete would necessarily include the user ID that deleted it as part of the audit. The account deleting the row will always be the service account for the web app in the database. It's our internal user IDs that we're interested in as to "who" is deleting the row, and that's what we want to capture.

Would this be good practice?  What would be the pros and cons of using this method, and would it impact performance much compared to just using DELETE statements for row deletion?


texts: SSIS - Setup OLEDB Connection to SQL Server Always On Listener

texts:
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).

I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I specify MultiSubnetFailover? I don't see an option for it. And do I have to add Server=TCP in the connection?

When I run the package the connection string looks like this...

Data Source=myTestServer\mssql;User ID=TestUser;Initial Catalog=TestDB;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;


texts: ibdata1 increases when firing update queries

texts:
I have read a lot of posts which explain that the only solution to stop the growth of an ever increasing ibdata1 file is to:

Take a dump of all databases
Set innodb_file_per_table in the mysqld section of my.cnf
Clean the data directory (except mysql)
Restart the MySQL Server and load the dumps using mysqldump.

However my problem starts once I have done all these steps. I still see a scenario where the size of my ibdata1 file increases. The scenario is explained below:

I start MySQL Server 5.5 with a clean data directory (only the mysql directory is present), the size of the ibdata1 file reads 18 MB. Next steps follow:

I have the innodb_file_per_table entry already mentioned under the mysqld section of my.cnf.
Now I load some tables from mysql dump files (all of them have InnoDB storage engine specified) using mysqldump. I observe that ibdata1 size has not changed and separate ibd files have been created which is expected according to the innodb_file_per_table setting
I add a new column say, ColumnA (int, NOT NULL, UNSIGNED, DEFAULT 0) to a table, say Table A. The storage engine is InnoDB and contains 9 million records. So far so good, the size of ibdata1 file remains the same (18 MB)
I now update the values in ColumnA using the following query: update TableA set ColumnA = crc32(someothervarcharcolumnfromthistable);
I observe that during the update the size of ibdata1 consistently grows and grows past 300 MB.

Why is this happening? Does this mean I have to do the cleanup-restart-restore activity mentioned at the beginning every 'x' months, or am I missing something?

Edit 1:
This question is not a duplicate of this question answered by RolandoMySQLDBA. Reasons follow:

It says what ibdata1 stores in general. However it does not clarify when all precautions are taken against its increase, why would it still do so when performing operations on a particular table.
Though it is very informative, however it outlines the corrective measures that can be taken, does not highlight preventive measures post the activity is done.

In case it is not clear from my question, I wanted to get the opinion of experts on preventive measures once I have set innodb_file_per_table for ibdata1 not to grow.


texts: Multiple Ad-Hoc Statements taking longer than previous days

texts:
We have a Data Warehouse product which runs a number of ad-hoc (IE SQL Text, not stored procs or dynamic SQL) every night.

The build took significantly longer last night than previous nights and looking at the DW Product's log, I can see a number of INSERT INTO.... SELECT.... FROM statements that have taken longer (approx 10 queries each taken circa 30 mins longer than normal) than the previous night

I have taken a couple of these queries and tried to run them in SSMS and am still seeing the slow performance. I then updated stats on the tables of these queries which has made no difference.

When they are running, I run sp_BlitzFirst and can see the usual CXPACKET and LATCH_EX waits I am used to seeing on the server and nothing out of the ordinary.

The queries have never been the best performing and are in need of tuning but I can't see what would cause this seemingly random nose dive.

The build runs overnight when there is no other activity running. I have observed today and I could see no blocking (Although I wouldn't think it was down to blocking as they run slow in SSMS now)

I have overnight Perfmon traces and they are not showing anything unusual for environment either.

What could the problem be here? It sounds parameter sniffing-esque but these are ad-hoc queries. Nothing has changed on the server.


texts: Weed out certain rows where timestamp is within interval

texts:
Given a table structure:

create table contacts (
  contact_id int(8) not null auto increment, #primary key
  pid int(8) not null,                       #personal id
  contakt_time datetime,                     #time of contact
)

Where each person can have multiple contacts with the system at any point in time.

What is the best way to filter out contacts by a given person in a certain time frame?

E.g. if:

SELECT * FROM contacts where pid = 1201;

yields:

contact pid     contact_time
-----------------------------------
10530   1201    2014-01-04 14:33:00
11475   1201    2014-01-13 18:16:00
13460   1201    2014-02-03 12:23:00
14220   1201    2014-02-10 16:55:10
15599   1201    2014-02-24 21:03:34
16488   1201    2014-03-06 12:45:00
16499   1201    2014-03-06 14:55:00
16900   1201    2014-03-10 11:18:00
19108   1201    2014-04-02 10:40:00
20954   1201    2014-04-22 17:24:00

I would need to remove all contacts except the first one in a given time window. For instance, all contacts happening within a 5 hours window after each contact. In the example above, the result would no longer include contact #16499 as this is too close to contact #16488 (< 5 hours apart).

Thank you for any help


texts: Query to get the physical file name of the database

texts: Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ). How can i dynamically extract just the tempdb.mdf off of that string? I was thinking of using SUBSTRING() + CHARINDEX() but cant figure it out since im new to this. CHARINDEX() only searches for a substring from left to right. Is there a function which searches a particular string from right to left? That way i can just search from the last back slash base from my example. Any input would be very much appreciated. Thanks!


texts: Parameter Sniffing and Multiple Plans in Cache

texts:
We have a multi-tenanted database. FirmID is the partition key and we have lots of different firms.
I am running into a parameter sniffing issue and I am having a heck of a time getting around it.
I would rather not use any [Options] on the query.
My latest thought was to change the name of the parameter I am using for the firm. In the snippet below you will see that instead of using @FirmID I called it @Firm611 where 611 is the actual firm of the ID. This will give me a unique query for every firm.
select
    c.ID [_cid],
    c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm611
and (c.Name is not null and c.Name!='')

select
    c.ID [_cid],
    c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm625
and (c.Name is not null and c.Name!='')

After running Brent Ozar's sp_BlitzCache, I found that it is just compiling down to the same query and causing duplicate cache entries:

My question is am I reading that result right? Even though I am changing the parameter name, is it really still using the same plan and parameter sniffing?


texts:
I'm writing a client-server application where geotagged photos are main content.

Backend is based on ASP.NET Core tech stack.

Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

I'm not really sure if i need relational DB for such a simple task.
Also i'd love to see search by distance from point made by DB not by my code.
Good integration with .NET Core is a great plus.

I've worked with SQL Server from MSFT, but it seems a bit overweight for this simple task.

Could you guys with some experience in area, advise a DB for my task?


texts: On a typical COW filesystem, is the database WAL redundant (in theory)?

texts:
EDIT: I was incorrectly assuming that all COW filesystems have a WAL. IT seems that BTRFS doesn't, so this question essentially applies only to ZFS.

Typical COW filesystems, like ZFS, have their own form of WAL (ZIL, in the case of ZFS).

On a non-COW filesystem, the DB WAL makes sense for both performance (because it's sequential) and integrity reasons (because of the lack of write integrity guarantees); on a COW filesystem, those roles are fulfilled by the FS (WAL).

Because of this, doesn't storing DB data on a COW filesystem make the DB WAL redundant?


texts: Ensure total order in table via constraint

texts:
Suppose I have a table

create table log (id serial, logged_at timestamp, event_type integer)

Can I efficiently (without making every row writable only once or by some special user) ensure that
∄ (r1, r2) ∈ log X log (r1.id > r2.id ∧ r2.logged_at > r1.logged_at)

So events in log partially ordered by time and we can always have total order via monotonically increasing sequence.


texts:
Server Detail: SQL Server 2008 R2

Production Set Up:

In a production environment, I have a DBMR job scheduled to take transaction log backups for all the databases present in a DB server.

This job is scheduled to run every 15 minutes, 7 days a week. Presently this job does not overwrite previous log. So hard disk space is an issue over time.

Question:

It seems logical I should be overwriting a new entry over the older one.But not being very familiar in this area, is it normal practice to overwrite transaction logs.

Any suggestions/advise much appreciated.


texts: How to remove temp tables from SQL Server?

texts:
I want to remove temp tables that I was using in my script in SQL Server 2008. I can't find a way to remove it without writing a few lines of code. I have not been able to find code that works properly. Is there any way to do this without writing code at all? If not, what should be my approach? Will copying my code in a new script and then creating same name temp tables help?

Any help/advice/suggestion much appreciated.


texts: Select where datetime is greater than other datetime which may not exist

texts:
I have a table with a field eventtime of type datetime and a different table with a datetime field.

I need the minimum eventtime that is greater than the greatest datetime field from the other table which may not have any rows. If there are no rows in the other table, I just need the minimum eventtime.

My query is

SELECT
    min(eventtime) time_min
FROM
    `cel`
WHERE
    eventtime > (SELECT COALESCE(MAX(other_datetime), 0) FROM `call`)

This gives me exactly what I want. The problem is there are about 6.5 million records in the table and this query takes about 10 or 11 seconds to run.

I tried using other values instead of 0 in the coalesce such as an empty string, and '1000-01-01 00:00:00' thinking that a type conversion was causing a performance hit, but those did not help at all.

mysql explain for the query

Should I add an index on the eventtime field?
Is 0 an appropriate value to compare against a datetime field?
If not, what is the right way to represent the minimum datetime value for comparison?
Is there a better way to achieve this to optimize for execution time?


texts: One database per company for "security" reasons

texts:
Possible Duplicate:
  Is that a good idea to use one database for 50.000+ shops?

We are developing an internet application in C# with MVC 4.
It's connected to a database which will contain a companies customers and their turnovers, also the products.

Naturally,these companies want their data secured, and also the data needs to be brought from the database to the web application as quickly as possible.

We are unsure what is the most secure and performance effective approach:

Have one smaller database for each company, containing their customer data. This way the only "weak" human link is to code the connection string correct depending on who's logging in.
Use one single database containing every companies data and all their customer data. This will naturally be a very much larger database, and possible(?) more room for error, leading to one employee seeing another companies turnovers. A critical situation.

What solution might be most secure, and the fastest in crunching numbers and processing it to the web application?
is this a common "security" solution, to minimize human error by giving each company their own database?


texts: Strange UTC offset time zone parsing in Postgres

texts:
Postgres exhibits some strange behaviour when parsing time zones, or I just don't understand how it works.

From the documentation:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

  The example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

This seems to imply that 'MST' is interchangeable with 'UTC-7' but the behaviour is opposite to what you'd expect.

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'UTC-7';
Result: 2001-02-17 08:38:40

Instead of using the time zone 'UTC-7' it's using 'UTC+7', which is 14 hours different.

Using ISO 8601 time zone notation also yields the opposite from the expected result:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-07:00';
Result: 2001-02-17 08:38:40

The only ISO 8601 notation that threw an error was the -0700 notation.

Other gibberish notations are accepted, even though they don't make any sense at all:

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7MST';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST-7';
Result: 2001-02-17 08:38:40

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '-7+7';
Result: 2001-02-17 08:38:40

Can someone help me understand how time zones, specifically offset notation works, or is supposed to work, in Postgres.


texts:
I have the following tables:

-- Table structure for table `Operator`
--

CREATE TABLE Operator (
  OperatorID int(11) NOT NULL,
  Name varchar(25) NOT NULL,
  Street varchar(25) NOT NULL,
  Town varchar(25) NOT NULL,
  Postcode varchar(25) NOT NULL,
  Email varchar(35) NOT NULL,
  Phone varchar(20) NOT NULL,
  PRIMARY KEY (OperatorID)
)

-- --------------------------------------------------------

--
-- Table structure for table `Route`
--

CREATE TABLE Route (
  RouteNumber varchar(5) NOT NULL,
  Frequency int(11) NOT NULL,
  Start varchar(5) NOT NULL,
  Destination varchar(5) NOT NULL,
  PRIMARY KEY (RouteNumber)
)

-- --------------------------------------------------------

--
-- Table structure for table `RouteOperators`
--

CREATE TABLE RouteOperators (
  RouteNumber varchar(5) NOT NULL,
  OperatorID int(11) NOT NULL,
  Proportion int(11) NOT NULL,
  PRIMARY KEY (RouteNumber, OperatorID)
)

I'm attempting to retrieve the phone numbers and emails of operators who serve bus stop 7628 (i.e Start or Destination = 7628) with the following query:

SELECT Phone,Email FROM Operator, Route, RouteOperators
WHERE (Route.Start = 7628 OR Route.Destination = 7628)
AND RouteOperators.RouteNumber = Route.RouteNumber
AND Operator.OperatorID = RouteOperators.RouteNumber

The query doesn't return anything, although there are also no errors. Can anyone see what I'm doing wrong?


texts: Is my system configuration restricting me to psql:i386?

texts:
When I try to run psql on 64 bit sid I get this error message:

/usr/lib/postgresql/9.4/bin/psql: symbol lookup error: /usr/lib/postgresql/9.4/bin/psql: undefined symbol: PQconninfo

I needed to run some 32 bit programs so I had to install the 32bit version. But I wonder if it added some dependencies that is causing the current problem. If its an architectural conflict then I'd like to install a 64bit psql as well, but I still need them both. Reinstalling postgresql-client doesn't change things so it seems that a 64bit is not being installed unless psql is 32bit only.

Running ` ldd /usr/lib/postgresql/9.4/bin/psql' produces:

    linux-gate.so.1 (0xf77d7000)
    libpq.so.5 => /lib/i386-linux-gnu/libpq.so.5 (0xf7782000)
    libssl.so.1.0.0 => /usr/lib/i386-linux-gnu/i686/cmov/libssl.so.1.0.0 (0xf771c000)
    libedit.so.2 => /usr/lib/i386-linux-gnu/libedit.so.2 (0xf76e4000)
    libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf7572000)
    libcrypto.so.1.0.0 => /usr/lib/i386-linux-gnu/i686/cmov/libcrypto.so.1.0.0 (0xf738d000)
    libkrb5.so.3 => /usr/lib/i386-linux-gnu/libkrb5.so.3 (0xf72b3000)
    libcom_err.so.2 => /lib/i386-linux-gnu/libcom_err.so.2 (0xf72ae000)
    libgssapi_krb5.so.2 => /usr/lib/i386-linux-gnu/libgssapi_krb5.so.2 (0xf725b000)
    libldap_r-2.4.so.2 => /usr/lib/i386-linux-gnu/libldap_r-2.4.so.2 (0xf7201000)
    libpthread.so.0 => /lib/i386-linux-gnu/libpthread.so.0 (0xf71e5000)
    libdl.so.2 => /lib/i386-linux-gnu/libdl.so.2 (0xf71e0000)
    libbsd.so.0 => /lib/i386-linux-gnu/libbsd.so.0 (0xf71ce000)
    libtinfo.so.5 => /lib/i386-linux-gnu/libtinfo.so.5 (0xf71a9000)
    /lib/ld-linux.so.2 (0x565a8000)
    libk5crypto.so.3 => /usr/lib/i386-linux-gnu/libk5crypto.so.3 (0xf7177000)
    libkrb5support.so.0 => /usr/lib/i386-linux-gnu/libkrb5support.so.0 (0xf716a000)
    libkeyutils.so.1 => /lib/i386-linux-gnu/libkeyutils.so.1 (0xf7165000)
    libresolv.so.2 => /lib/i386-linux-gnu/libresolv.so.2 (0xf714e000)
    liblber-2.4.so.2 => /usr/lib/i386-linux-gnu/liblber-2.4.so.2 (0xf713d000)
    libsasl2.so.2 => /usr/lib/i386-linux-gnu/libsasl2.so.2 (0xf711f000)
    libgnutls-deb0.so.28 => /usr/lib/i386-linux-gnu/libgnutls-deb0.so.28 (0xf6fd9000)
    libz.so.1 => /lib/i386-linux-gnu/libz.so.1 (0xf6fbc000)
    libp11-kit.so.0 => /usr/lib/i386-linux-gnu/libp11-kit.so.0 (0xf6f59000)
    libtasn1.so.6 => /usr/lib/i386-linux-gnu/libtasn1.so.6 (0xf6f43000)
    libnettle.so.6 => /usr/lib/i386-linux-gnu/libnettle.so.6 (0xf6f06000)
    libhogweed.so.4 => /usr/lib/i386-linux-gnu/libhogweed.so.4 (0xf6ed1000)
    libgmp.so.10 => /usr/lib/i386-linux-gnu/libgmp.so.10 (0xf6e42000)
    libffi.so.6 => /usr/lib/i386-linux-gnu/libffi.so.6 (0xf6e39000)

I am running Debian Sid, but I don't think that is the problem.


texts: How to restore percona database from kubernetes?

texts:
I got the backup works by setting these on the helm chart values (it works according to the documentation):
    google-cloud-storage-s3:
      type: s3
      s3:
        bucket: gcp_bucket
        credentialsSecret: google-cloud-storage-s3-backup
        region: us-west2
        endpointUrl: https://storage.googleapis.com/

It's uploaded successfully on the google cloud storage:

Then I tried to restore the backup:
gsutil -m cp -r \
  "gs://gcp_bucket/percona-2021-05-06-00:00:11-full.md5" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full.sst_info/" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full/" \
  .
| [1.1k/1.1k files][  2.7 GiB/  2.7 GiB] 100% Done 942.3 KiB/s ETA 00:00:00
Operation completed over 1.1k objects/2.7 GiB.

sudo apt install percona-server-server percona-xtrabackup-80

The documentation said:
service mysqld stop
rm -rf /var/lib/mysql/*
cat xtrabackup.stream | xbstream -x -C /var/lib/mysql # --> where is this from?
xtrabackup --prepare --target-dir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
service mysqld start

The problem is, I could not find any file named copy-backup.sh or xtrabackup.stream in the backup directories, is there other way to restore the backup, also all files compressed with lz4?


texts:
With MySQL 5.5, I want to change the partitioning scheme of a very large InnoDB table from "hash" to "range". Can I do it while the table is accepting DML? If not, what is the best-practice work-around?

Thanks!


texts: AlwaysOn Availability Group listener port selection

texts:
Simple question (I hope!)

Why would you use a non-default port for an Always On AG listener?

I haven't found a good post (yet) outlining advantages vs disadvantages.

Currently, I can think of three reasons to use something other then 1433:

Following Sql Server security hardening guidelines.
Avoiding port conflicts (as per https://msdn.microsoft.com/en-us/library/hh213417.aspx#SelectListenerPort).
Being able to identify db calls to specific AG's quicker in network traces and such.

Are there any others people can think of?


texts: Best way for naming schemas in SQL Server 2019

texts:
Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions:

0,1,2 etc. to version procedures and functions. For example [1].[GetFirstName] is the newest version of procedure but we have also node where we has to use [0].[GetFirstName] procedure (for example to compare efficiency between old and new).

For tables and views we are not using schemas but I am thinking about adding below:

conf - Configuration - for table which store confiuration data
dict - Dictionary - for table which store dictionaries
log  - Logging - for table which store logs
proc - Processing - for tables which wille be use to massive updates and insert commands (~ 300 insert/updates per seconds)

My questions:

Do you think that using procedure version as schema is a good idea ?
Is it good idea to have different idea for schemas in tables/views (responsible of table) and in procedure/functions (versioning)
Do you think that using below schema preposition is a good idea ? What problems can I have in future in process of addministration/change on production ?
Do you have any idea (except schemas) how to name tables with config,dictionaries,processing ? Is it worth to divide them in groups by for example add substring at the end (StatusDict, EventDict, StatusConfig, EventConfig, TransactionProc) etc. ? I would add that I am using naming convenction from: SQL Server Name Convenction. Beacuse of naming tables like I describe above the ID for columns will be StatusDictID, EventDictID instead of StatusID, EventID.

Tank you in advance for answers.


texts: MySQL Query on bad designed table with multiple Selfjoins

texts:
i have to create a report out of a bad designed database (I cannot change)

It saves different Formulars into a form_values table

---+-------+-----------+-------
id | value | form_item | entry
---+-------+-----------+-------

value = input value (DB type = text but can also hold numeric inputs dates and so on)
form_item = input name (example: "name:", "birthday:")
entry = id to formular

My Problem on big Formulars I have to self join multiple times to get all form values and make conditions out of it

Example query:

    SELECT fv.entry as 'entry' FROM form_values fv
                INNER JOIN form_entry_links fel ON fel.child = fv.entry
                LEFT JOIN form_values fv2 ON fv2.entry = fel.child AND fv2.form_item = 117
                LEFT JOIN form_values fv3 ON fv3.entry = fel.child AND fv3.form_item = 118
                LEFT JOIN form_values fv4 ON fv4.entry = fel.child AND fv4.form_item = 119
                LEFT JOIN form_values fv5 ON fv5.entry = fel.child AND fv5.form_item = 120
                LEFT JOIN form_values fv6 ON fv6.entry = fel.child AND fv6.form_item = 121
                LEFT JOIN form_values fv7 ON fv7.entry = fel.child AND fv7.form_item = 122
                LEFT JOIN form_values fv8 ON fv8.entry = fel.child AND fv8.form_item = 123
                LEFT JOIN form_values fv9 ON fv9.entry = fel.child AND fv9.form_item = 124
                LEFT JOIN form_values fv10 ON fv10.entry = fel.child AND fv10.form_item = 125
                LEFT JOIN form_values fv11 ON fv11.entry = fel.child AND fv11.form_item = 126
                LEFT JOIN form_values fv12 ON fv12.entry = fel.child AND fv12.form_item = 127
                LEFT JOIN form_values fv13 ON fv13.entry = fel.child AND fv13.form_item = 128
                LEFT JOIN form_values fv14 ON fv14.entry = fel.child AND fv14.form_item = 129
                LEFT JOIN form_values fv15 ON fv15.entry = fel.child AND fv15.form_item = 132
                WHERE
                fv.entry IN(4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,41,43,45,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,72,75,78,79,82,83)
                AND fv.entry IN (43,48,51,52,54,55,57,58,60,61,63,64,65,67,68,70,75,82,4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv.entry IN (4,5,6,10,11,12,16,17,18,22,23,24,28,29,30,34,35,38,78,79)
                AND fv2.value = 0
                AND fv3.value = 0
                AND fv4.value = 0
                AND fv5.value = 0
                AND fv6.value = 0
                AND (
                    ( fv2.value > 0)
                    OR( fv7.value > 0)
                    OR( fv8.value > 0)
                    OR( fv9.value > 0)
                    OR( fv10.value > 0)
                    OR( fv11.value > 0)
                    OR( fv12.value > 0)
                    OR( fv13.value > 0)
                    OR( fv14.value > 0)
                    OR( fv15.value > 0)
                )
                 GROUP BY fv.entry

This is really slow and so the report takes over 1 hour on a small database with 15000 entries

Maybe someone can help me to make a performant query out of this mess,
thank you so much


texts: Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

texts:
Any suggestions on how to deal with this error:

TITLE: Microsoft SQL Server 2017 Setup
------------------------------

The following error has occurred:

VS Shell installation has failed with exit code 1638.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0x5B39C8B9%25401434%25403

------------------------------
BUTTONS:

OK
------------------------------

This is a new laptop running

SQL Server 2016 express
Visual Studio 2017
SSMS 2017

I tried uninstalling anything related to SQL Server or Visual Studio.

Log: [3500:3970][2017-11-03T16:25:20]e000: Error 0x80070666: Cannot install a product when a newer version is installed.

Detailed results:
  Feature:                       Full-Text and Semantic Extractions for Search
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638

  Feature:                       Database Engine Services
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638


texts:
I want to import data from Excel into an existing table in a SQL Server database.

I have a column like Gender and the values are Male and Female in Excel. I want to insert only the first character into database, like M and F.

How should I achieve this thing in SSIS for SQL Server 2008?


texts:
Something strange happened today.

I reinstalled my OS and then when I reinstalled MySQL workbench some of my old data was still there. I think the actual local db is empty, but all the connections, models, and listed servers are still there. I can't view which users still exist because I get an connection error. The server is running, though.

I have a backup. I do a dump nightly. I was going to restore from that, but I expected a clean slate when I reinstalled. Should I delete everything before moving ahead with the restore?

Does anyone have an idea why/how that happened?


texts: How to find out which records are causing an exception in a select statement?

texts:
I have a select query that I use as part of an insert statement,
but it raises the following exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Here is the definition of the table I want to insert to:

IF OBJECT_ID('[dbo].[tbl_crbHistory]') IS NOT NULL
DROP TABLE [dbo].[tbl_crbHistory]
GO
CREATE TABLE [dbo].[tbl_crbHistory] (
[Id]               INT              IDENTITY(1,1)   NOT NULL,
[ClientReference]  VARCHAR(50)                      NOT NULL,
[Status]           INT                              NOT NULL,
[DateAdded]        DATETIME                         NOT NULL
            CONSTRAINT [DF__tbl_crbHi__DateA__4F8DAFFA] DEFAULT (getdate()),
CONSTRAINT   [PK_tbl_crbHistory]  PRIMARY KEY CLUSTERED    ([Id] asc),
CONSTRAINT   [FK_tbl_crbHistory_ref_crbStatus]
            FOREIGN KEY ([Status]) REFERENCES [ref_crbStatus]([statusId]))

GO

CREATE NONCLUSTERED INDEX [idx_crbDateAdded]
   ON [dbo].[tbl_crbHistory] ([DateAdded] desc)
   INCLUDE ([Status], [ClientReference])
CREATE NONCLUSTERED INDEX [idx_clienteReferenceStatus]
   ON [dbo].[tbl_crbHistory] ([ClientReference] asc)
   INCLUDE ([Status], [DateAdded])
GO

EXEC sys.sp_addextendedproperty
          @name = N'MS_Description', @value = N'Stores history of CRB status change',
          @level0type = N'SCHEMA', @level0name = [dbo],
          @level1type = N'TABLE', @level1name = [tbl_crbHistory];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Primary Key',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Id];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description',
         @value = N'unique id for each applicat per organisationId',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [ClientReference];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'CRB Status',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Status];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Time stamp',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [DateAdded];

the complete statement, including the select and the insert:

INSERT INTO tbl_crbHistory (clientReference, status, dateAdded)
SELECT tc.pushedClientReference as ClientReference,
       tc.pushedStatusId as [Status],
       tc.createdOn as DateAdded
FROM tablebackups.dbo.tmp_colagbgdata tc
LEFT JOIN tbl_application_crb_initialData ci
ON tc.PushedClientReference = ci.ClientReference
WHERE ci.clientReference <> ''
AND tc.pushedStatusId  IN ( SELECT STATUSID FROM dbo.ref_crbStatus)

The column tc.createdOn is a datetime and it is breaking my code due to the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Is there any way(s) I can select the record(s) that are causing this?

If I could find these records in this or similar situations, I could deal with them and re-run my query at a later time.


texts:
I give an example to show my problem.
I create 2 table as the following:

CREATE TABLE a
(
    id INT
)

CREATE TABLE b
(
    name NVARCHAR(10),
    id INT
)

Then insert data into these tables

INSERT INTO a VALUES(1),(2)
INSERT INTO b VALUES('Kan',1),('Michael',2)

Next,I create indexed view that join these tables via id

CREATE VIEW a_b
WITH SCHEMABINDING
AS
(
SELECT a.id,b.name FROM a INNER JOIN b ON a.id=b.id
)

CREATE UNIQUE CLUSTERED INDEX ix_a_b
ON a_b(id)

INSERT INTO a_b VALUES (3,'Joe') will be wrong

As I know about view:

views do not store data,just saved queries
but indexed view that stored data physically like table in the database.So why I don't insert,delete from a_b?

And what I know about VIEW is right or wrong?Help me improve?


texts: PostgreSQL : Prevent SQL Injection in pl/pgsql function

texts:
I am trying to handle sql injection related to startdate and enddate in the below pl/pgsql function.

Let's say If I provide startdate as '2019-10-02' and end date as the following :

2019-10-16', 'YYYY-MM-DD') AND rid in ('R1857745') group by rid, dcid UNION ALL SELECT 'R1857745'::character(8) , 'D099901'::character(8), 1, 1, 1, 1 FROM pg_shadow --

The above value for enddate creates a serious SQL vulnerability.

create function A(
 in startdate varchar(10),
 in enddate varchar(10))
RETURNS setof AB AS $$
BEGIN
 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent>=to_date(''' || startdate || ''',''YYYY-MM-DD'') and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent>=to_date(''' || startdate || ''',''YYYY-MM-DD'') and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent<=to_date(''' || enddate || ''',''YYYY-MM-DD'') '
  group by rid, dcid';
END;
$$ LANGUAGE plpgsql;

As startdate and enddate are of variable type date, so we can do validation with date formats. But, I really don't want to do that because in this scenario I am more concerned about single quote.

I know that we can escape ' by using 2 single quotes i.e. ''

Is there any public function available in postgreSQL which can escape single quote, so that we can use the same for all type of variables in where clause or in other places.

I tried using quote_literal, but it's not doing the job for me


texts:
In my mongodb database,there is only one document below:
  {
      "_id" : ObjectId("5226efc5ccf23796f9590f67"),
      "count" : NumberLong(126045),
      "eissn" : "1466-5034",
      "grade" : 0.0,
      "issn" : "1466-5026",
      "journalAlias" : ["International journal of systematic and evolutionary microbiology", "Int J Syst Evol Microbiol", "Int. J. Syst. Evol. Microbiol."],
      "journalName" : "International journal of systematic and evolutionary microbiology",
      "ccNameCount" : [{
          "ccName" : "ATCC",
          "paperCount" : 1,
          "strainCount" : 2
        }, {
          "ccName" : "ATCC",
          "paperCount" : 3,
          "strainCount" : 4
        }, {
          "ccName" : "BCCM/DCG",
          "paperCount" : 5,
          "strainCount" : 6
        }, {
          "ccName" : "BCCM/IHEM",
          "paperCount" : 0,
          "strainCount" : 0
       }
    }

What I want ,is to find out how many different ccNameCout.paperCount whose ccName is "ATCC".So ,in my java code ,I write like this:

coll.distinct("ccNameCount.paperCount",new BasicDBObject("ccNameCount.ccName","ATCC")),

but the result is 4, instead of 2.

I know the process of function distinct.Firstly , it search the collection whose ccNameCount.ccName is "ATCC", and then ,it will do a distinct() in these found documents.

But what I need, is to find how many different paperCount whose ccName is "ATCC".
Anyone can help me ?


texts: Insert trigger checking for specific column

texts:
I have got an insert trigger on a table. The table got a field named 'source'.
The source field could be populated with values b or left empty as in null.
i want the trigger only to fire if the source field is not populated with 'b'.
The below trigger codes deosnt give me error, but never runs.
if i remove the if condition it runs, but i need to check for value of 'b' before it should run.

Create TRIGGER TriggerName
ON tableName
FOR INSERT
AS
BEGIN

DECLARE @source nvarchar(50)

SELECT                  @source = (select source  from inserted)

             if not (@source = 'b')
                begin

                INSERT INTO ATable
                (field1,field2,field3)
                SELECT i.field1,i.field2,i.field3
                FROM inserted i

                    end
end


texts: Changing Schema ownership and side effects

texts:
I am trying to understand why changing schema ownership is causing permissions to be revoked.

I am having a hard time understanding schema ownership change.  Here are the steps I have taken in order.

Created two logins hr and it
Created two users(hrread and itread) for those logins
created two database roles hr_read_only and it_read_only
Created two schemas with names hr and it and populated some tables
Added hr_read_only and it_read_only roles as owners of hr and it schemas, respectively
added users hrread and itread as hr_read_only and it_read_only roles

If I try accessing hr tables as hrread user, I am able to.  However, when I change the owner of hrschema to dbo, I am not able to access tables within the hr schema.

After some time I determined that the role is owner and users are part of that role.  So keeping the ownership of hrschema to dbo, I have added securrables to the role hr_read_only (select permissions on objects with hr schema).  I am able to access now.

But when I change ownership of hrschema, this is affecting the users too, they were not able to access hr tables, only one way is I have to add back securables again.

Can you please help me understand why changing schema ownership is causing this and any best recommendation to avoid this.

SCRIPT:
create login itreadonly with password='Password123$'
create login Hrreadonly with password='Password123$'

create user hrread for login hrreadonly
create user itread for login hrreadonly

CREATE ROLE [hr_read_only]
CREATE ROLE [it_read_only]

create schema hr
create schema it

i went ahead and added hrread and itread users part of hr_read_only and it_read_only role.

2.I changed ownerships of hr and it schema to hr_read_only and it_read_only respectively

3.i am able to access now all tables with hrschema when executing as hrread

4.Now i went and changed the ownership of hrschema to DBO

5.I was not able to access now when executing as hrread

6.So i went back to hr_read_only role and added securables giving select permission on tables with hr schema.

7.I was able to access now all hr tables

8.I changed back hrschema ownership to someother role other than hr_read_only and it_read_only and dbo

9.Now i am not able to access any tables with hr schema when trying to execute as hrread,when i tried to see securables for hr_read_only ,i am not seeing anything

I am not able to understand why this is happening,even though i gave securables


texts: Is there a way to have SQL work in parallelism with two computers?

texts: Amateur here. I have a couple of the same old 4 core computers that I am trying to use to beef up some automatic query scripts that run every evening for my hobby. I recently found MPICH to have the two computers communicate with each other.  I also found documentstion that allows postresql to use more than the default number of cores (It was set at 2). I am hoping there is some way of being able to use all 8 cores to run some SQL queries.  Is this possible? Any thoughts or recommendations are appreciated.


texts:
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no keys on the table).

For that, I would do:

 SELECT COUNT(*) FROM
     (
        Select
            X, Y, Z
        From
            A
     )
     MINUS
     (
        Select
            X, Y, Z
        From
            B
     )

Now, I really don't need to know count(*) value, as long as there is one mismatch between the data, i.e. values tuple exists in A but not in B, I know that the tables are not identical. Is there a way for me to say this in SQL? I.e. as soon as MINUS encounters one mismatched value, return a value from a query indicating that?

Thanks!


texts: Data Type to use for Exchange Rate in SSAS Multidimensional

texts: So we have a cube setup that does a many-to-many currency conversion. Until this point, the Exchange Rate measure group has used a Currency data type for the exchange rate, but I didn't realize that that's limited to 4 decimal places. With the only other Data Types in SSAS being Double and Single, what are people using for their exchange rate data types when the rates may need to be more then 4 decimal places? How many decimal places would a double be accurate to from a FX rate perspective?


texts: Database gives "ORA-01109: Database not open" error when it IS open

texts:
Running Oracle Database 12c on Windows Server 2012

Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple solution of opening the database via alter database open, then checking the status via select status from v$instance to ensure it is open will fix the issue. However, anytime I check the status, it is already in an OPEN state. alter database open doesn't do anything as it's already open.

Anyone have any idea why I'm encountering this issue? Any help would be appreciated.

Thanks


texts: Importing files with a mix of decimal separators

texts: Using SQL Server Management Studio 18.3 I want to import data (semicolon separated text file) that has some columns with dot as decimal separator, and some with comma as decimal separator. I can't find an option to import both types on the fly, forcing me to choose nvarchar for either of them, and then transform it. Changing locale first would not help either, obviously. (I find it odd that the import wizard is not locale-agnostic.) How do you best resolve this problem?


texts: How to retrieve the definition behind statistics added to tables

texts:
Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like '__WA_Sys_*' that are added by Sql Server.

I need to re-write some of them and add more, but there are too many to do them manually with Management Studio.


texts: Database tuning and possibly unexpected results

texts:
(This is cross-posted from stackoverflow; I initially put it in the wrong place.)

So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so hopefully I can verify I'm not missing some performance tweak.  Some details first:

I'm using java and the database is H2 embedded (although I'm seeing similar results regardless of database).
I've profiled / timed it and the problems are definitely in the database queries, specifically the executeQuery() call.
For this example the database table has 60K rows but my query involves a subset of 25K rows yet takes a full 1/2 second to return.

Here's how I've set up the database table:

CREATE TABLE rows (
  id @AUTOTYPE@ PRIMARY KEY,

  x VARCHAR(255) NOT NULL,
  y VARCHAR(255) NOT NULL,
  z @TIMETYPE@ NOT NULL
)

CREATE INDEX rows_index ON rows(x, y, z);

(Note that the parameterization is for auto increment and timestamp, respectively)

And my query looks like this:

SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z DESC

Now, I only care about the first row, but it needs to be the newest row, so in java it looks like this:

PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, "foo");
stmt.setString(2, "bar");
stmt.setMaxRows(1);

Notice the last line has the setMaxRows() set to 1.  To reiterate, the entire 'rows' table has about 60K rows and the query would return 25K rows if I wasn't setting it to return only 1.

So am I doing everything I can?  Even though 1/2s (0.5s) is certainly not a long time it doesn't seem like 60K records should take that long, especially when it should be indexed.  And I know time is relative, so this is just a modern workstation, not running on some sort of load-balanced server.

Thoughts?  Thanks for taking a look.


texts: MySQL incremental backups per database (not global)

texts:
While searching for free/open-source MySQL incremental backups solutions I have found a number of ways to do incremental backups and all of them seem to either rely on "logical" way of backups/restore like mysqldump and binlog (or LSN) or "physical" way (like Percona's xtrabackup). However, in both cases the only scenario implied is backing up the database globally (all databases).

I am looking for the way to provide incremental backups per database: there are two db servers (master and slave) and each handles lots of databases. Each of these databases belong, let's say, to different customers and some of them tend to grow really big. While software upgrades database schema sometimes changes and before the upgrade we must make sure we are on the safe side so backup is a must but full backup is definitely not a way to go due to database sizes.

Please advise how this can be solved or at least suggest some ways to look for solution.


texts: Connect to Oracle DB 9i using SSH TUNNEL via putty

texts:
I am trying to connect to an Oracle database behind NAT. Therefore I create an ssh tunnel using putty.

This is the network  information:

global IP example: 122.54.34.12
LAN IP example: 192.168.3.103
Oracle database port : 1521

and this is a snapshot of the putty ssh tunnel settings

After I have opened an ssh session  to the global IP with putty,
I try to connect to the Oracle database with SQL console through the ssh tunnel  but get the following error message:

sql > connect username/password@127.0.0.1/service-name
ERROR:
ORA-12170:TNS Connect timeout occurred


texts:
I am new with postgresql.

When I attempt to restore database a .sql.gz file into the database with psql, the restore does not complete when the database size is too large (32Gb, for example).

These reason again I restore empty tables.
How can I resolve this issue?

psql -U postgres -d dbname -f <filename>


texts:
We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of good reasons.

I've written a small script that allows me to dump the old MySQL tables into a PostgreSQL database, prepending their name with oldapp_*

We are starting to write some data migration scripts which are basically:

insert into foobar(c1, c2, ..., cn)
select c1, c2, ..., cn from oldapp_foobar where ...

Now, this app uses surrogate keys extensively (Django doesn't allow composite primary keys and encourages the use of surrogates heavily). For the first migration scripts I've tentatively skipped the surrogate keys from the insert/select, and thus PostgreSQL generates new ids automatically.

This works, of course, but when migrating foreign keys this is not a good idea. I'm pondering several approaches, but I'd appreciate some input here.


texts: Can PosgreSQL databases be restored from physical files?

texts:
We had an EC2 instance running PostgreSQL. We created a new EBS volume for our more important DBs and stored them on the new volume by creating new tables spaces and setting their location. The system DBs were left in the default tablespace and location. Unfortunately the instance was accidentally terminated. We are trying to restore PostgreSQL with the databases.

The databases were on the EBS volume. We have spun up a new instance and have mounted the EBS volume and we can see the files. Is there any way we can get our files back?


texts: MySQL Return average for every unique id per day

texts:
I have a table:

  Unique ID, DeviceID, Temperature, Epoch

And 3 sensors (DeviceID's) sending temperature every minute to the database.

I would like to select to return AVG(Temperature) for every DeviceID per day since the beggining. So the results should come up as:

10-1-2016 Device1 AVG(Temperature)=32
10-1-2016 Device2 AVG(Temperature)=13
10-1-2016 Device3 AVG(Temperature)=14

10-2-2016 Device1 AVG(Temperature)=13
10-2-2016 Device2 AVG(Temperature)=13
10-2-2016 Device3 AVG(Temperature)=13

10-3-2016 Device1 AVG(Temperature)=22
10-3-2016 Device2 AVG(Temperature)=22
10-3-2016 Device3 AVG(Temperature)=22

Would you be so kind to help out with this please?

Tried following:

SELECT DATE(FROM_UNIXTIME(Epoch)) AS NodeDate, DeviceID as NodeID,AVG(PM1) AS Temperature FROM Data WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE()-7 GROUP BY NodeDate ORDER BY NodeDate ASC

The results are bringing only one DeviceID per day.


texts: Unique code based on category - a sort of auto_increment

texts:
I have to create a unique code to every user registering. The thing is that the unique code needs to be generated based on which city the user is from.
So the tables I have are:

CREATE TABLE `microreg`.`users` ( `user_id` INT NOT NULL
AUTO_INCREMENT , `user_code` CHAR(10) NOT NULL , PRIMARY KEY (`user_id`),
UNIQUE (`user_code`));

user_id is for unique identification of users in DB.
 user_code is
   the readable code generated for the use of users. They access their
   account with that unique code.

CREATE TABLE `microreg`.`districts` ( `district_id` INT NOT NULL
AUTO_INCREMENT , `district_name` VARCHAR(50) NOT NULL , `district_code`
CHAR(4) NOT NULL , PRIMARY KEY (`district_id`), UNIQUE (`district_code`));`

This contains as about 20K districts with unique code assigned.

say,

Trichy      ITRY
Coimbatore  ICBE
Kolkata     IKOL
Delhi       IDEL
...
New York    UNYK
Washington  UWDC

So, when user registers with Trichy as district, he will be generated with

user_id   : 1
user_code : ITRY000001

second user from same city

user_id   : 2
user_code : ITRY000002

another one from New York

user_id   : 3
user_code : UNYK000001

user_code is unique with respect to district. [User with id 1 and 3 are not same]

So far, I fetched the maximum value of the selected district of the user

SELECT RIGHT(MAX(user_code),6) FROM `users` WHERE user_code LIKE 'ITRY%';

and then increment it by 1 and then add it back with district_code appended to it.

INSERT INTO `microreg`.`users` (`user_id`, `user_code`) VALUES (NULL, 'ITRY000003');

This method works perfectly when there is only one user updating at a time. But this started creating problem when multiple users concurrently started registering.
Since unique is set, two users with same user_code was generated and creates overhead.

Previously, for another application, I created unique tables for every code (like district here) and added the user to that table and then appended back to original table (like users here).
In that case I had only 4 types of code (like district here). But for this app, I have got about 20K districts. I couldn't get a spark for this. (For information, I use PHP with mysqli for this application) Thanks.


texts: Why does NOT IN with a set containing NULL always return FALSE/NULL?

texts:
I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.

What's the best way to understand this? I thought of NULL as something without a value, and therefore wasn't expecting the query to fail, but obviously that's not the correct way to think of NULL.


texts: SQL Server Cluster Service Online Pending Status

texts:
I have 2 Nodes of SQL Cluster running SQL 2012 Ent over Windows 2012 R2 and i don't know suddenly what happened the service is not coming online. Under Failover Cluster Mannager > Roles > SQL > Resource > SQL Server (instance-name) the status us Online Pending and then Failed.

Cluster have only below evetns;

The Cluster service failed to bring clustered role 'SQL Server (instance-name)' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.
Cluster resource 'SQL Server (instance-name)' of type 'SQL Server' in clustered role 'SQL Server (instance-name)' failed.
Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Event Viewwer;

Fault bucket , type 0
Event Name: Failover clustering resource deadlock
Response: Not available
Cab Id: 0

Problem signature:
P1: SQL Server (instance-name)
P2: SQL Server
P3: ONLINERESOURCE
P4:
P5:
P6:
P7:
P8:
P9:
P10:
Attached files:
These files may be available here:
C:\ProgramData\Microsoft\Windows\WER\ReportQueue\Critical_SQL Server (instance-name)_217169f34c33c838a6522b285697633e938ebe_00000000_03a6e263
Analysis symbol:
Rechecking for solution: 0
Report Id: 11f7e038-5f26-11e7-80fb-7a2f1397e813
Report Status: 4100
Hashed bucket:

Kindly suggest what could be wrong.


texts: Database organization for mirroring / partitioning / multistore

texts:
I know that is kind of generic question, but there is not other way to find a good answer.

I have a requirement to create a network of a server and few simple PCs.
My boss wants to have the database in the server but also mirroring (the whole DB) it in all the machines, because he want the PC's to work even in the case that the network is down!!!

We'll be working with a lot of data, each PC (a) controls an industrial production line and (b) records information about every action into the DB. The data under consideration are not sensitive.

My idea is that each machine will have a part (same tables but only part of the data) of the whole DB and they will update the "mother" DB.

Is my idea a good approach and, if yes, how could I accomplish such a scenario?

Is there a better approach?


texts: Better way to update table with value from another table

texts:
I'm trying to rewrite the following query to make it more efficient:

update c set c.cartype = (select Id from carTypes where carType = 'Coupe')
from cars c
inner join doorcount dc
    on c.Id = dc.carId and dc.Value=2
       and c.carType= (select Id from carTypes where carType = 'Sedan')

What I've come up with so far is:

update c set c.cartype = ct.id
from cars c
inner join cartypes ct on ct.carType='Coupe'
inner join doorcount dc on c.Id = dc.carId and dc.Value=2
inner join carTypes ct2 on c.carType = ct2.Id and ct2.carType = 'Sedan'

This looks better to me, but is performing almost exactly the same.

Is there a more efficient way to perform this update?


texts:
In SQL Server 2012 I am trying to attach a .mdf file to a database. The thing is there were still open transactions when my server was shut down. Then I reinstall the server and I have deleted the .ldf file.

Now when I try to attach the .mdf file in SSMS, an error says without .ldf file a .mdf file cannot be attached because there was open transaction.

Does anyone know a solution?

Many thanks!


texts: what is the structure of a Mysql multi-column index?

texts:
For example:
CREATE TABLE test(
col_1 INT,
col_2 INT,
col_3 VARCHAR(11),
col_4 VARCHAR(11)  )ENGINE = INNODB;
CREATE INDEX mul ON test(col_2,col_3,col_4);

Sorry,my English is not good,I mean create a multi-column index, what is this index structure？


texts: SSIS Raw Data Import Into SQL Server 2008 R2 - Truncation and Condensed Data Type

texts:
[Intro]
I have written a two pack SSIS solution to perform a scheduled hourly update of data from multiple tables in a single db (SRC) to single table on another db (TRG).

I get the following errors and I understand them:

[ADO NET Destination [328]] Error: An exception has occurred during data insertion,
    the message returned from the provider is:
* String or binary data would be truncated.
* The statement has been terminated.

I Know that the data from SRC in some columns is too large for the data in the TRG matching column.  For instance SRC.CompositeAddress is nvarchar(50) and TRG.Addr is char(6)  (same size as Condensed Data Type) which does not make sense for an address

[Question]
My question revolves around not really understanding the condensed data type.  Aside from data in SRC being too large for data in TRG I am wondering if condensed data type is affecting my SSIS import?


texts: What is the exact relationship between a database transaction and locking?

texts:
This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response.

As a long-time application developer, I know at some level what a transaction is (I use them all the time).  Leaving aside transaction isolation levels for the moment, at a high level a transaction allows a block of work to be completed entirely or not at all, and allows for a certain amount of isolation from other database-modifying activity.

I also know what (in various databases) a lock is, or at least how one behaves (if I lock a table in some way explicitly, then no other process or thread can update anything about that table).

What I am most distinctly not clear about is: in various databases, when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

That is, it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking.  Is this transaction-initiated, tranasction-hidden locking the same sort of locking that various databases let me access through constructs such as SELECT FOR UPDATE or explicit LOCK commands?  Or are these two concepts completely different?

Again, I apologize for the naïveté of this question; I am happy to be pointed to more foundational sources.


texts: Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)

texts:
I have a table us_customers that looks like this (with hundreds of thousands of rows):

+----------+----------+
|    id    | us_state |
+----------+----------+
| 12345678 | MA       |
| 23456781 | AL       |
| 34567812 | GA       |
| 45678123 | FL       |
| 56781234 | AZ       |
| 67812345 | MA       |
| 78123456 | CO       |
| 81234567 | FL       |
+----------+----------+

... and I want to SELECT a sample of n customers from each us_state.

Is there a way to do that cleanly in PostgreSQL 9.3?

I can get one customer from each us_state easily with:

SELECT DISTINCT ON (us_state) id
FROM us_customers
ORDER BY us_state;

But if I want, say, three customers from each state, is there a way I can do this without running the same query multiple times?


texts: Does MySQL update indexes on virtual columns on tables that are being replicated from a master?

texts:
We use statement based bin log master-slave replication with MySQL 5.7.
We use the slave for analytics and so add additional indexes and things not required on the master.
I recently tried to add a virtual column and indexed it.
Unfortunately, it seems that none of the new data being replicated ends up in the index.
When I query the new rows, the generated column values are present and correct but when I filter the table on the generated column values, none of the rows created since the index was created are returned.

Am I doing something wrong here or is this not supported with bin log replication?


texts: server running- mysqladmin error- Development Source Tree

texts:
I'm frustrated!
I have installed mysql using Development Source Tree(version 5.7.5) in Ubuntu 14.04 32-bit.

Starting

I started mysql in safe mode using:

mysqlnew@dpaks:~$ mysqld_safe &
[1] 4548
mysqlnew@dpaks:~$ 141024 11:18:58 mysqld_safe Logging to'/usr/local/mysql/data/dpaks.err'.
141024 11:18:58 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

However, on entering 'mysqladmin version', I get the following error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'deepaks'@'localhost' (using password: NO)'

If I give a prefix '--skip-grant-tables', mysqladmin starts and gives the following:

  mysqladmin  Ver 8.42 Distrib 5.7.5-m15, for Linux on i686

  Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.

  Server version        5.7.5-m15

  Protocol version  10

  Connection        Localhost via UNIX socket

  UNIX socket       /tmp/mysql.sock

  Uptime:           7 sec

  Threads: 1  Questions: 2  Slow queries: 0  Opens: 84  Flush tables: 1  Open tables: 79  Queries per second avg: 0.285

So, what is happening with the grants table?

Permissions:

For the purpose of running mysql, I created a user as well as a group by the name mysqlnew.
My entire installation at /usr/local/mysql is owned by mysqlnew. Permissions
were set to 777 recursively for the entire directory except for the data directory.
Data directory was given 744.
my.cnf was set to 755 for user mysqlnew. Its temp file was owned by root.
mysql.sock had permissions 777

Server is running? Yes, it is!

Its listed in process status.

mysqlnew  5022  4341  0 12:22 pts/8    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysqlnew  5154  5022  3 12:22 pts/8    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/dpaks.err --pid-file=/usr/local/mysql/data/dpaks.pid --socket=/tmp/mysql.sock --port=3306

Also, the netstat command gave me the following:

tcp        0      0 localhost:mysql         *:*                     LISTEN      4680/mysqld

Error Log
The error log says the following:

2014-10-24T05:49:00.835656Z 0 [Note] InnoDB: 5.7.5 started; log sequence number 1296682
2014-10-24T05:49:00.854718Z 0 [Warning] Failed to setup SSL
2014-10-24T05:49:00.854736Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2014-10-24T05:49:00.854757Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2014-10-24T05:49:00.854795Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2014-10-24T05:49:00.864657Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2014-10-24T05:49:01.399455Z 0 [Note] Event Scheduler: Loaded 0 events
2014-10-24T05:49:01.399499Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.5-m15'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

which means that server is running! Right?

Conf file Following is an excerpt from my my.cnf file located in etc directory:

[client]

port=3306
socket=/tmp/mysql.sock

[mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user = mysql
bind-address = 127.0.0.1
port = 3306
socket = /tmp/mysql.sock

I also verified that mysql.sock is being created in /tmp directory.

Question: Why am I getting an error when I start mysqladmin without using the prefix '--skip-grant-tables' while the server is running?

Any help would be wholeheartedly appreciated

PS: I have read thoroughly and tried all the solutions given in similar questions here but to no avail.

Update 1

When I gave --skip-grants-table and then when ran 'mysql', its working! But why should I skip it?


texts: In SQL Server, what's the impact of a checkpoint to log records in log buffer?

texts:
Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about this by the top names in the SQL Server industry.
From Kalen Delaney's "Microsoft SQL Server 2012 Internals":

Checkpoint operations also write log records from transactions in progress to disk because the cached log records are also considered to be dirty.

"from transactions in progress", yes that's reasonable, since the log records for committed transactions were already written to disk. So it basically means all unflushed log records will be flushed.
From Itzik Ben-Gan's "Understanding log buffer flushes" at https://sqlperformance.com/2018/11/sql-performance/understanding-log-buffer-flushes:

SQL Server needs to harden dirty data pages, e.g., during a checkpoint process, and the log records representing the changes to those pages were not yet hardened (write ahead logging, or WAL in short)

Are the the log records in log buffer that corresponding to the dirty pages all unflushed log records in log buffer? I'm not sure. Is Itzik's description basically means the same thing as Kalen's description?


texts: Query to change all field in a given mysql table?

texts:
I have a table with a timestamp field called "time".

I have many records in this table and "time" has value like: 1343296642, 1343308689...

I need to run a query to subtract 2 years of time to those value.

Can I do this.


texts:
We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) and import our exported database tables into a fresh new MySQL 8.0 installation
Should it be successful or should we do something else in the middle for it to work?


texts: Reading table names from content of MySQL files

texts: My hard drive was unclean closed and all of my files was moved by fsck into lost&found directory without original names. Unfortunatelly, I had no backup of every database. I'm sure I can detect extensions based on size of files. Is it possible to restore somehow table names from content of mysql files (both MyISAM and InnoDB)? Then I could restore original file names. I use MySQL 5.7.


texts: Does Postgres support stored column labels that can be queried?

texts: I'm not a DBA but seem to recall that there was a SQL database that stored column labels (preferred column headings that can be printed on reports). Does Postgres support labels such as these?


texts:
Currently I have these settings. I am adding a new db called mydb3. How to enable the replication for this new db? Currently I have copied the table schema into both master and slave. What other changes must I do ? Can I just add binlog_do_db=mydb3 and replicate-do-db=mydb3 on both master and slave?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_file_per_table
innodb_lock_wait_timeout=120
innodb_buffer_pool=5G
innodb_log_file_size=512M

server-id=1
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=mydb1

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

For the slave here is the settings.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_file_per_table
server-id=1339951115
master-host = ***
master-user = ***
master-password =***
master-port = 3306
replicate-do-db=mydb1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts: SQL to merge related documents into 1 query row (MySQL)

texts:
I'm working with the following data structure:

users

| field| value
| ---  | ---
| id   | 123
| email| example@example.com

fields

| field    |value
| ---      |---
| parentId |123 -> users.id
| name     |Bank
| value    |WBC

Users have a one-to-many relationship to fields, a single user will have multiple fields with different names.

I'm trying to construct a query that would ultimately output something like:

| field | value
|---    |---
| id    |123
| email |example@example.com
| bank  |ANZ

So there's a new column added to the query for each unique name value. I do know all the possible name values up front.

My query is pretty standard so far, it's grouping but obviously grouping discards all but 1 of the joined model. Is there a way around this?

SELECT users.id, fields.name
FROM users
LEFT JOIN fields ON (fields.parent_id= users.id)
GROUP BY users.id
ORDER BY updated_at DESC
LIMIT 100 OFFSET 0


texts: Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?

texts:
Our clients are insisting that we now encrypt ALL their SQL Server data at rest, which must include tempdb. I think I have the following options available –

Bitlocker entire drive(s)
TDE (but the cost of Enterprise edition breaks our support model for these clients!)
Always-Encrypted (but more a GDPR/Personal information solution, not for whole databases) – and cannot then “wildchar” search these fields, which is a requirement.
EFS the User databases location on disk
Some 3rd party application that does SQL Server encryption
Others?

Any recommendations appreciated (for SQL Server 2016 STANDARD edition only)


texts: how to upgrade postgresql 9.0.4 to latest with minimum downtime

texts:
I have master-slave-slave setup for postgresql 9.0.4 in our production setup.
We need to upgrade it to latest (11.X), so my question is what would be the procedure to do so with as little downtime as possible.

Also what options (method wise) i have to upgrade it.


texts: What is the optimal response time for file growth

texts:
We normally set our file growth in percent (10%,20%) or fixed size (10 MB, 20 MB etc). If the disk response time is too high for the requested size to grow, then users will be affected and have to wait until the file growth completed. We can see the response time in micro seconds at default trace file column named as 'Duration'.

My question is, what is the optimal response time for every growth in milliseconds?


texts:
I've got a couple of tables that get cleared out regularly by a cron job on a web server. However when these tables are completely empty it looks like there is still data in them.

e.g. This database is showing 2 tables that have 0 rows, yet their size is not 0. If i truncate the tables they do go down to 0.

The only additional data I can think of that might be contained in these tables are the auto increment number of the id fields, but considering the number in the first table is only at 29 as of the screenshot 117Kb seems quite a lot for such a small bit of data.

Sorry if this is an incredibly obvious answer, I don't know a huge amount about how databases actually work.


texts:
sys.tables lists all user tables from a single database, But it doesn't have their schema name.

If I know existing databases when creating the code, I can use USE to browse all databases, query sys.tables for its tables, and insert the list into a temptable to use later.

But, is there an easier way to do it, and with a unique query be able to retrieve all user tables from all user databases with their schema?


texts: Analyse what caused a pagesplit afterwards?

texts:
Ok normally we have 0-20 page splits per second, but last night it spiked to ~500.

Now is there any way to figure out what caused it?

I know i can run the following to find mid page splits but it doesn't go that far back.

SELECT COUNT(1) AS NumberOfSplits, AllocUnitName, Context FROM fn_dblog(NULL,NULL) WHERE Operation = 'LOP_DELETE_SPLIT' GROUP BY AllocUnitName, Context ORDER BY NumberOfSplits DESC

I have both full backups and log backups if that helps?


texts: Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)

texts:
My department currently relies heavily on CmdExec steps in SQL Server Jobs, called using a SQL Server Login.

For example

osql /e /n /S SERVER01 /U USER /P PA$$$ /d DATABASE_01 /i "\\LOCATION\OF\SQL\SCRIPT\scriptfile.sql" /o "\\LOCATION\OF\SQL\LOG\scriptfile.log"

For a lot of reasons (not least security and the expectation that this method will become obsolete) I'd like us to move away from this model and replace with a mixture of stored procedures and SSIS packages.

I've proposed this several times and I'm always told that we can only move in this direction if we can recreate the detail of logging possible by the method above, which our department has come to rely on quite heavily. The method above outputs the contents of the script fed in along with any server messages including all rowcounts and error messages, in context.

This does admittedly mean that errors that wouldn't count as errors for SQL Server are far more easily picked up -  Unexpectedly low row counts etc. You could put in as much error handling as you liked and never get something as easy to follow as the logging from this method, so I can see this side of the argument.

To sell my boss on a move away from cmdexec steps I'd like to find a method of recreating something like the output of the logging that cmdexec currently gives us - perhaps to SQL tables rather than files (although either would be fine) - in SSIS packages and stored procedures.

I'm familiar with using raiserror for error handling - I use this extensively in stored procedures - but the task here is not just to catch SQL errors, but to catch all that cmdexec logging catches and ideally in context of the script being executed... Short of writing a stored procedure and executing it after every step in every script I can't see a way of doing this.

Also I'm keen to hear about best practice approaches to logging for people who don't use cmdexec steps. I've learnt SQL in this department and this is the only way I've ever known of doing things.

Increasingly for my own development I try to write stored procs with error handling. And if I come across issues that don't throw errors, I do my troubleshooting by rerunning the code interractively on a dev server so I can monitor the step by step information. This is less convenient than the full logging we get from cmdexec but more secure.

Do other people do more than this? Is there a better way?


texts: How MySql Join two tables with Checkboxes results stored as "string" data format?

texts:
Suppose I have 2 tables obtained from a web form submission, with some checkboxes results stored as "string" format as describerd here in "hobby" column https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=aff4e50085be40930e223b00a1ac81f3,
How could I join this two tables and obtain the following result?

sub_id
datimkt

1
Basket,Gas,SUV

2
Basket,Cabrio,Football,hybrid

I've tried to execute the following query, but it only takes the first result from the checkbox result field.
SELECT GROUP_CONCAT(DISTINCT submission_id)sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY d.datimkt;
SELECT*FROM result2;


texts: Can we use SQL Express to send messages to SQL Standard using the Service Broker?

texts:
We want to use SQL Express at our local sites but to still be able to push data back to our head office using the Service Broker.  SQL Server at the head office is 2008 R2 Standard.

Looking at the feature lists of SQL Express 2008 R2 for Express it says "Client Only", what does this mean in relation to our needs?


texts: Is it possible to retrieve a history of the remote connections to the MongoDB including the IP addresses and the executed commands?

texts:
For some reasons I disabled the user access control and somehow all collections have been wiped out. So I want an activity log to find out how this happened

I need a list like this:

|  Date/Time           |  IP           |  Command           |
|----------------------+---------------+--------------------|
|  2018/1/1  18:20:50  |  127.0.0.1    |  show collections  |
|  yyyy/m/d  hh:mm:ss  |  x.x.x.x      |  executed command  |

OS: Ubuntu 18.04
MongoDB: 3.6.3


texts:
When running the CTE below, it fetchs me the data I want.

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

However when I'm trying to create a view on it like below

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error

  ERROR:  The column « Site » has been specified more than once

  ********** Error **********

  ERROR: The column « Site » has been specified more than once
  WSQL State :42701

I've tried to put the view name before Site, namely a or d or "Axis_Reference_All" but none of it works.

Questions are the following:

Is CTE allowed with view creation on postgres? I guess no but it is unclear.
What would be the best way to create a view with my CTE? That CTE has been built that way because it is improving performance.


texts: In the context of another principal what permissions hinge on the identity of the database owner?

texts:
By "database owner", here, I mean the principal whose SID is sys.databases.owner_sid.

This answer to the question of best practices for database ownership includes the following:

  The answer here comes down to priorities: Convenience xor Security? Pick one.

  One issue is that the database owner is used to determine permissions for certain operations. The sa account, by its very nature, is not restricted from anything (outside of maybe placing restrictions on itself, but now we're just being silly). I'm not sure what that list of operations is, but using a low-privileged SQL Server login as the owner is definitely one way that people discover what those operations are ;-) (hence why so many people prefer to use sa, even if it is a poor choice; more on that in a moment).

This suggests that the permissions granted to the database owner are factored into permissions checks during operations invoked in the security context of other principals.  This sounds reasonable and I can imagine a circumstance where changing the database ownership resolved some permissions issue, but I have not seen that play out first-hand.

This answer seems to support this suggestion:

  the most important thing: the database owner has important side effects, specifically the EXECUTE AS context. This later problem is what burns most users. Since Service Broker makes extensive use of EXECUTE AS (the message delivery has an implicit EXECUTE AS context, as well as queue activation that has an explicit one) is usually Service Broker users that discover this problem first.

This suggests that EXECUTE AS context could lead to unintended escalation of privileges to those of the database owner.

Microsoft's "Summary of the Permission Check Algorithm" does not, however, seem to mentioned the database owner:

  Security context

  This is the group of principals that contribute permissions to the access check. These are permissions that are related to the current login or user, unless the security context was changed to another login or user by using the EXECUTE AS statement. The security context includes the following principals:

  The login
  The user
  Role memberships
  Windows group memberships
  If module signing is being used, any login or user account for the certificate used to sign the module that the user is currently executing, and the associated role memberships of that principal.

Microsoft's Chart of SQL Server Permissions also doesn't seem to mention owner_sid (although a lot seems to hinge on the db_owner role, but that is a different matter).

Furthermore, Microsoft's "Understanding Execution Context" does not seem to make any special mention of the database owner.

This raises the following questions:

What permissions, if any, hinge on sys.databases.owner_sid?
Are there operations that SQL Server invokes using sys.databases.owner_sid to which the "SQL Server Permission Check Algorithm" does not apply?
Does EXECUTE AS ... context somehow allow a normal user to inherit the permissions of sys.databases.owner_sid?


texts: Oracle extents - How can I have so many with this small size?

texts:
This is a different question than the one with the almost-identical title.

Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents are fine, I read Tom Kyte too).

I have a table with the following characteristics:

Initial Ext: 1.44GB
Next Ext:    1MB
Num Extents: 26
Size:        4.18MB

In this case, I got the size by scanning 10% of the table rows.  With an initial extent size so high, and the data so low, how did further extents get added?  I am certain that the table did not become huge, then shrink drastically back down again.

Thank you.


texts:
I'm trying to find out, if Oracle's newest database release 12c comes per default as an in-memory database or does it use TimesTen for this feature?
According to Wikipedia (Oracle Database) it "utilizes TimesTen technology" for "In-Memory Database Cache".
Are those two different databases or is the one like a feature of the other?