Tuesday, September 24, 2013

[how to] ERROR 1166 (42000) at line 65203: Incorrect column name ''

[how to] ERROR 1166 (42000) at line 65203: Incorrect column name ''

ERROR 1166 (42000) at line 65203: Incorrect column name ''

Posted: 24 Sep 2013 08:49 PM PDT

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message:

  $ mysql -u root < mydata.sql  ERROR 1166 (42000) at line 65203: Incorrect column name ''

MySQL Version is 5.5.32-0ubuntu0.13.04.1 and this is a head from SQL file:

  $ head -n 10 mydata.sql   -- MySQL dump 10.13  Distrib 5.5.22, for Linux (x86_64)  --  -- Host: localhost    Database:   -- ------------------------------------------------------  -- Server version   5.5.22-55-log

This is a snapshot of the line that making an issue:

  $ sed -n '65200,65203p' < mydata.sql   /*!50001 SET character_set_client      = latin1 */;  /*!50001 SET character_set_results     = latin1 */;  /*!50001 SET collation_connection      = latin1_swedish_ci */;  /*!50001 CREATE ALGORITHM=UNDEFINED */

How can I resolve this issue?

MySQL 1366 Error on Replication Slave

Posted: 24 Sep 2013 06:01 PM PDT

I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs. Running MySQL 5.5.34 on Windows Server 2008 R2.

I'm trying to track down a problem that has occurred twice now on on of the pairs.

I get a 1366 error "Incorrect integer value '\x00' for column Flag at row 1" for the following call:

UPDATE mytable SET Flag = 0 where Id = 10

When I issue the show slave status command.

On the other pair I don't ever get this error when replicating.

The type of Flag is tinyint(1).

I haven't been able to identify any differences between the two pairs yet.

Ideas?

Mysterious merge replication problems creating a subscription

Posted: 24 Sep 2013 09:08 PM PDT

We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got error messages during initialization and found in the verbose error logs this problem:

Msg 1934, Level 16, State 1, Procedure DDLTrigger_Index, Line 19 INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

...fast forward to now. In experiments we found that yes it is possible to create a merge publication on the publisher and subscribe on our subscriber server, but only if using a different database. Went through this just to rule out permissions/etc and to determine it was truly the subscriber db that was the problem.

So, it seemed logical to remove replication from afflicted DB in hopes of cleaning out any buried metadata mess:

sp_removedbreplication @dbname = 'sickDB' @type = 'all'

So far so good. However now, I get the exact same error, but much earlier in the process. Specifically, when executing:

/****** Begin: Script to be run at Subscriber ******/  use [sickDB]  exec sp_addmergepullsubscription   @publisher = N'publisher',   @publication = N'publication',    @publisher_db = N'goodDB',    @subscriber_type = N'Local',     @subscription_priority = 0,      @description = N'hope this works', @sync_type = N'Automatic'

(above edited slightly).

Is there some remaining prob lurking in the metadata? The original theory on the ANSI_PADDING stuff was there were in fact some tables in the subscriber DB w/ computed columns thus that seemed related. However some flipping of ANSI_PADDING settings (and Googling) didn't really lead to a conclusive answer.

Thanks so much for reading and let me know of any other info I can provide that may help.

Oh yeah, publisher and subscriber are both SQL Server 2005.

Does it make a difference to add the DEFAULT keyword in CREATE TABLE?

Posted: 24 Sep 2013 10:12 AM PDT

The MySQL manual says:

CREATE DATABASE db_name      [[DEFAULT] CHARACTER SET charset_name]      [[DEFAULT] COLLATE collation_name]    ALTER DATABASE db_name      [[DEFAULT] CHARACTER SET charset_name]      [[DEFAULT] COLLATE collation_name]

The database character set and collation are used as default values for table definitions if the table character set and collation are not specified in CREATE TABLE statements.

Someone made a point in the comments, saying:

It's not at all clear what difference it makes to specify DEFAULT or to omit it. So if you specify DEFAULT then that charset becomes the default for tables for which you don't specify an explicit charset. But what if you omit DEFAULT? Does that prevent tables from using a different charset from the database? This should really be explained above.

Does it make any difference if the DEFAULT keyword is specified, or are CHARACTER SET and DEFAULT CHARACTER SET just exact synonyms?

dcexec login failure

Posted: 24 Sep 2013 11:23 AM PDT

I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec.

Isolating that out, I can reproduce the issue by running:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1

I run the above command from a PowerShell window that I've opened with RunAs Administrator. The account I am running the above with is a domain account that is a local administrator on the server. In addition, the account also has the sysadmin role on the SQL instance.

The error that I get is:

SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540,   Subcomponent: Generate T-SQL Package Task,   Description: An error occurred with the following error message:   "An error occurred while verifying the result set schema against the output table schema.   The data collector cannot connect to the management data warehouse. : Login failed.  The login is from an untrusted domain and cannot be used with Windows authentication."..    The master package exited with error, previous error messages should explain the cause.

That is the same error I see in the history on the SQL Server Agent job.

Note that if I execute dcexec with a bad instance name, such as: 

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1

Then, as expected, I get a different error:

Failed to get the SQL server instance id from the given instance name.

Any ideas on how to successfully login to the SQL instance with dcexec?

Max Memory settings on Active/Active SQL Server 2008 R2 Cluster

Posted: 24 Sep 2013 02:04 PM PDT

  • SQL Server 2008 R2 active/active cluster (on VMware vSphere 5.1)
  • 2 instances on each node
  • 16GB RAM per node.

The only time the instances are on the same node is when I am patching the other node.

Brent Ozar's "maximum server memory" recommendation is to leave Windows 4GB or 10%, whichever is more. http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/

Since this a cluster, how should I set the max memory on each node? Should I treat each as a standalone server? This would make sure memory on each node is not wasted. However, in a node failure, the max memory total for all 4 instances would exceed system memory of the single node. Will this cause any issues in the timeframe until we get the second node recovered? Do I need to lower the max memory setting on the 4 instances until the secondary node is recovered? Or is SQL Server smart enough to keep working (using page file if necessary).

Why does MySQL allow HAVING to use SELECT aliases?

Posted: 24 Sep 2013 05:01 PM PDT

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount  FROM Sales.Orders  GROUP BY YEAR(orderdate)   HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5  Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

  • Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
  • Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?

Understanding disk requirement to make fail over cluster

Posted: 24 Sep 2013 10:14 AM PDT

Please take a look at the following screenshot.

enter image description here

I am trying to make SQL Server 2012 Fail Over Cluster using two nodes. As you know we need to have a shared SAN which will be used by both nodes to store data but I have some confusion regarding it.

I was under the impression that we need one drive for e.g. Z for "Temp DB directory" which will be shared by both nodes to store data.

But someone told me that these will be two drives actually for each node and for these nodes it will appear as one. So I am not sure what is true. May be we are both saying same thing!?

Problem with PostgreSQL and the geqo optimizer

Posted: 24 Sep 2013 11:33 AM PDT

I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types. These are the variables I use for geqo:

SET geqo = true;  SET geqo_threshold = 2;

My queries have 8-12 FROM items involved, so the optimizer would definitely use the geqo algorithm.

I tried to add more variables to my queries to force the optimizer to choose an other plan:

SET geqo_effort = 2;  SET geqo_pool_size = 2;  SET geqo_generations = 1;

But like I said, I always get the same plan as the plan from exhaustive search.
Is there any possibility to show which algorithm (exhaustive search or geqo) the optimizer used to create the execution plan? Or are there other ways to force the optimizer to use geqo?

SQL Server 2008 Convert String to Datetime question

Posted: 24 Sep 2013 09:24 PM PDT

I have a string column in a table that display data as "CXL P/D 08/15/13"

I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type.

Cast(RIGHT(RTRIM(Trade_Date) ,8)as datetime) I'm trying this statement but it doesn't work

Conversion failed when converting date and/or time from character string.

Thank you

When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

Posted: 24 Sep 2013 02:33 PM PDT

There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option.

I believe Checksum was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

The problem involved is that we have a production database that went into production using SQL Server 2000 and has since moved to a SQL Server 2008 R2 server. Page Verify is set to None when I had been expecting it to be Torn Page Detection. Going back this amount of time we seem to think the DB was originally developed in SQL Server 7.0 then migrated to SQL Server 2000 and this may explain the observed result.

I was wondering when Torn Page Detection and Checksum became a feature of SQL Server, and how they behaves when migrated or upgraded to newer versions.

Enforce a constraint problem

Posted: 24 Sep 2013 12:54 PM PDT

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages. A phase can only have one stage that is "Current" or open at any one point in time. A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information.

Problem:

  • the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
  • You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
  • You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
  • the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
  • Is there anyway to enforce these business rules?
CREATE TABLE PHASE_FIRST_STAGE  (    PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE    STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table  );  ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01    FOREIGN KEY (PHASE_ID)     REFERENCES PHASE (ID),    FOREIGN KEY (STAGE_ID)     REFERENCES STAGE (ID));  COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';    CREATE TABLE STAGE  (    ID               NUMBER(9)           NOT NULL, --PRIMARY KEY    PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE    DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)    IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)    IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)  );    COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';  --not shown is a similar table called PHASE with a one phase => many type of stage relationship

Does TRIGGER improve the performance?

Posted: 24 Sep 2013 03:26 PM PDT

I understand that TRIGGERs are useful, as they take care of subsequent queries, but does they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...  UPDATE table2 ...  UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test  AFTER INSERT ON table1  FOR EACH ROW BEGIN  UPDATE table2 ... WHERE id=NEW.id;  UPDATE table3 ... WHERE id=NEW.id;  END

  1. Does we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?

  2. What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.

Restrict access to rows by usename

Posted: 24 Sep 2013 01:22 PM PDT

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,    PRIMARY KEY (`_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;    CREATE TABLE IF NOT EXISTS `_Address` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `owner` int(10) unsigned NOT NULL,    PRIMARY KEY (`_id`),    KEY `owner` (`owner`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;    CREATE TABLE IF NOT EXISTS `_Invoice` (    `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `owner` int(10) unsigned NOT NULL,    `address` int(10) unsigned NOT NULL,    PRIMARY KEY (`_id`),    KEY `owner` (`owner`),    KEY `address` (`address`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS   SELECT `a`.`_id` AS `_id`        FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)       WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));    CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS   SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`      FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)      WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`    ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;      ALTER TABLE `_Invoice`    ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,    ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Managing constants changes to database

Posted: 24 Sep 2013 03:22 PM PDT

Here's the deal, I'm in charge of creating a website that will host small web apps. This apps will be photo contests, creatives sentences and other similar gibberish. The deal is, since apps we'll be constantly changing and some new ones will come up what's the best way to save the config specs for each one? Should I create new tables and fields as I need? Or should I serialize this data and put it on one field?

In this case instead of having a table apps, I was thinking having a table for each app, this way I could have a more modular way of dealing with apps, making changes more easy, or more painfull (in case of the changes apply to all the apps)

Or if I serialize everthing, then I would only have one table and I won't have the need to make changes to the table, but this option seems a bit lame to me.

Alter mysql database engine doesn't work from cluster to other

Posted: 24 Sep 2013 10:22 AM PDT

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked.

It did not throw any error but did not work. I just want to know why this did not work as I got it from MySQL site that it won't work. I just need to know the reason for the same.

For client requirement I may need to alter the engine on installed database without losing any data.

Any Ideas or help that anybody can provide to resolve issue.

MS Access: error 3045 while linking to back-end

Posted: 24 Sep 2013 12:22 PM PDT

In our environment multiple users each work with their own copied front-end, with tables linked to a single networked back-end (both files are .mdb).

Each user has permissions to read and write to the location of BE, and both FE and BE are set to "shared access".

Recently a single user started getting error: 3045 "Could Not Use Database; File Already in Use" while trying to open forms using linked data.

The same error appears while trying to re-link the tables using the manager. All other users can still normally work.

Tried restarting his workstation, removing local files and re-copying the FE and it didn't work.

There is no .ldb file left after all users turn off the FE, compacting after that and restarting the FE of the unlucky user didn't help either.

Said user is working on AC2010, but some others are working on AC2007 and AC2003.

Please help!

edit: BE is stored on Windows Server 2003.

Security for Oracle linked server from SQL Server

Posted: 24 Sep 2013 04:22 PM PDT

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data from linked server.

So I tried mapping SQL Security User to Oracle user/pass by inserting it in "Local server login to remote server login mappings:" area then "For a login not defined in the list above, connection will:" set to "Not be made".

When I click OK, I get:

Access to the remote server is denied because no login-mapping exists

Is it not possible to map SQL login to a Oracle login? Is there any way to get this to work?

Optimizing mysql queries running on 50 million rows

Posted: 24 Sep 2013 12:31 PM PDT

Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip  FROM SystemEventsR  WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND   SysLogTagflag=1 AND   username !='')  GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,  COUNT(id) AS Records,   explanation, action   FROM SystemEventsR,oa_syslogtags  WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND   flagid = 1 AND DATE(timestamp) = DATE(NOW()))  GROUP BY SystemEventsR.SysLogTag   ORDER BY Records DESC

Any help would be appreciated.

Statistical Analysis of Data that has to be done in an order?

Posted: 24 Sep 2013 09:22 PM PDT

Bear with me - that is the first time try that in SQL Server, normally I have been doing that on the front end ;)

I a implementing some analysis on time coded data series. This is not super complicated stuff, but some of it requires some numbers we do not store in the database and that has to be calculated by aggregating the numbers in a specific algorithm IN ORDER.

To give an example:

  • I have a list of trades and I need to know the maximum loss we had in the account, so i need to aggregate the plus/minus and then take the most extreme negative and positive.

This can not be pre-calculated due to dynamic filtering - there are a number of filters that can be applied to the data.

So far - past - I pulled the data to the application, now for the standard stuff I plan to try to keep that in the sql server.

My problem now is - I can see how that works (acceptable) in SQL Server:

[TradeBegin],  Pnl,  SUM (Pnl) OVER (ORDER BY [TradeBegin] ROWS UNBOUNDED PRECEDING)  [Total]

But if I put that into a view... and then filter out rows, the Sum is still calcualted from the beginning. And I need a view because I want (need) to map that standard analysis data into an ORM (so dynamic SQL is out). Anyone an idea how to do that?

Record versioning and promotion

Posted: 24 Sep 2013 07:22 PM PDT

Let's say we have this hierarchy: 

   -World            --USA          ---WA          ----Seattle          -----Downtown          ------1st Ave          -------945 1st ave          ------3rd Ave          -----South          ----Bellevue            ---TX            ---MI            --Canada             ---BC

Now, this will not reflect real life completely, but.

  1. At location World we have some global settings. Let's say: list of Races, list of Languages, etc. All locations underneath it can access this data.

  2. Country level holds data about laws, currency, etc.

3.Each city can modify laws. After they modified the law, it is available to the city itself and to the regions within the city. After 3-step approval process, it can become a country level law, and all children will inherit new version of the law. Each city still can modify new version of a law to have changes.

  1. Every City has sandboxes. This means that different organizations within city can test out editing different versions of laws without affecting City master revision.

Currently we solve this issue like this:

We have table Location with HierarchyId
Then we have table Law that has columns StateType, Location_Id, etc.
StateType can be: Master (Available to all within country), Edited (Edited for a specific location), Approved (Approved at a specific location).

Because of this we have to use Table-valued functions in our application to filter specific records that are relevant to the current location. (Some laws can come from country level, some laws come from city level, some laws come from organization level). This makes database much slower.

We cannot use native unique constraints on our fields, because all fields must be unique only within a branch, so we have to use custom check constraints to keep data clean.

Also, insertion and editing records is much slower, since we have to promote some records to a country level, and then apply data transformation for other countries.

Currently we have around 150 tables, and we use a lot of joins.

I'm not sure what's the best way to model complex hierarchical data with versioning. (I'm thinking we need an enchanced version of source control..)

Are there any disadvantages to partitioning on financial year?

Posted: 24 Sep 2013 08:22 PM PDT

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.

The majority of queries are within the financial year/one partition. Either select * from sales where date time between '2013-05-01' and '2014-04-30 23:59:59' or some period within that year.

My plan is to have a range partition on an InnoDB table. e.g. 

PARTITION BY RANGE COLUMNS(datetime)  (PARTITION p0 VALUES LESS THAN ('1999-05-01') ENGINE = InnoDB,   PARTITION p1 VALUES LESS THAN ('2000-05-01') ENGINE = InnoDB,   PARTITION p2 VALUES LESS THAN ('2001-05-01') ENGINE = InnoDB,  ...

This means that the PK has to become PRIMARY KEY (index,datetime).

Are there any significant disadvantages to partitioning compared to having an unpartitioned table? I know that means the PK is now length 12 and all further indexes will have that prepended to it. Does that make a difference? The table needs to work faster on reads than writes, and there are a fair few indexes on it.

  • Is there any functional difference between partitioning on RANGE COLUMNS(datetime) and RANGE TO_DAYS(datetime) using MySQL 5.5+?
  • Is partitioning on datetime OK? One alternative is adding in a column for financial year and indexing/partitioning on that? (And then adding another index on datetime for other queries).

We do sometimes need to query the time across all time or over "the last X months", but this is pretty rare. The main advantages of moving to a single table is to eliminate the logic in the application working out which table to insert/update/select and not needing to calculate unions in those situations where we need more than one table.

MongoDB: move documents before capping

Posted: 24 Sep 2013 11:22 AM PDT

The cappedCollection concept works well for most of my projects where cleaning old data without care makes sense.

For another projects, I need a more complex and safe concept. The requirement is nearly the same as logrotate. The data is appended to the main collection, without compression/compact and no index except a timestamp for simple queries by time. => The focus is on writing and persistent data.

Similar to the logrotate rules, I'd like the main collection not to become too large => capped by size; if possible, capping by timestamp might be a plus.

This sounds like a cappedCollection, but I do not want any data loss when it's capped. The old data should be stored into another db's collection that must be compact:true and a non-capped collection. It's name depends on the current month and makes sure that there will be max 12 "archive" collections per year.

Example:

liveDB.mainCollection_capped grows and starts capping.

Before removing old documents, these are savely moved into archiveDB.compactArchiveCollection201303.

No data is lost and the main collection remains small and fast. Storing the data in another database avoids db locks, e.g. repairDatabase tasks on an archive file will not affect or delay the main collection.

Is there a good practice or how to achieve this - as reliable and automated as possible - without writing all the data transfer for a cronjob which handles the data transfer but should never ever be missed because data is lost if capping starts before old data is copied into the archive.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server

Posted: 24 Sep 2013 02:14 PM PDT

This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;  GO  RECONFIGURE;  GO  sp_configure 'Ad Hoc Distributed Queries', 1;  GO  RECONFIGURE;  GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?

SUPER privilege not defined for master user in Amazon MySQL RDS

Posted: 24 Sep 2013 05:22 PM PDT

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me following error

ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with variable using

SET GLOBAL log_bin_trust_function_creators = 1;

it gives me another error

Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I don't know how to solve this error.

Can anybody help???

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Posted: 24 Sep 2013 06:22 PM PDT

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Conceptually, I was thinking something like:

mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql

I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :)

Thanks for your time!

Deleting Data From Multiple Tables

Posted: 24 Sep 2013 03:01 PM PDT

Suppose,I've a table called UNIVERSITY containing universities name:

universityID    universityNAME  isACTIVE       7            GNDU             1       6            PU               1       5            PTU              1       8            LPU              1

Now these universities ID's has been(obviously) used in many tables within the database(name e.g.Education),Suppose 10 tables.

Q.Now what happen if i delete one university?

A.The universityID field in other tables becomes NULL.

But I don't want these,rather when I delete 1 university from UNIVERSITY TABLE,all its occurrences with Rows in all 10 table should get deleted.

What will be the shortest and easiest MySQL Query for this operation.

NOTE:I'm using PHP language.

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Posted: 24 Sep 2013 02:31 PM PDT

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:

SELECT COALESCE(c.FirstName                  ,(SELECT TOP 1 b.FirstName                    FROM TableA a                     JOIN TableB b ON .....)                  )

If, for example, c.FirstName = 'John', would SQL Server still run the sub-query?

I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog